mysql - 与现有层次结构数据库创建新关系

Question

我刚接触关系数据库，尤其是嵌套集合层次结构。我有一个现有的银行层次结构，其中银行分行是银行的“叶子”。我在尝试建立用户与银行之间的关系以及同一用户与每个分支机构必须提供的帐户类型之间的关系时遇到了困难。每个分支机构提供不同类型的帐户，每个帐户都有不同级别的规范。 (例如，一个分支机构可能只有一个级别的帐户规范，而另一个分支机构可能有四个级别)。我在下面创建了表格。

期望的结果:最终，我只希望能够运行一个选择语句来检索所选分支机构必须提供的所有帐户，直至达到其规范。我还想知道所选用户拥有什么帐户以及他们关联的银行。

我不想将帐户级别的数据添加到现有的银行层次结构中，因为我觉得这会使事情变得更加复杂。 (我的银行分行将来可能会有自己的假期)。我在下面缺少什么？

(银行)继承表:

顶层-“全局银行”--->2级-“环球银行-东海岸”&& "环球银行-西海岸"

我用以下数据创建了一个“分支”表:

 +---------+---------+---------+----------+---------+-----------+
| Id     |    name     | address 1     | telephone   | city     |                         
|
+---------+---------+---------+----------+---------+------------+
|     1 |GB-New York   |       East ST |212-555-9999 |   NY     |                           
|
|     2 |GB-California |       West ST |619-555-5555 |   CAL    |                  

然后我创建了一个“分支成员资格”表，其中包含用户 ID 和用户拥有帐户的分支。

 +---------+---------+---------+--------
| Id     |    userId     | branchId    |                          
|
+---------+---------+---------+---------
|     1 |        123 |       1 |                          
|
|     2 |        123 |       2 |

我想跟踪每个分支机构的账户类型，所以我创建了一个“branch_accounts”表

 +---------+---------+---------+----------+--+
| Id     |    account_type    | branch_id   |                         
|
+---------+---------+---------+------+
|     1 |Checkings        |       1 |                         
|     2 |Savings          |       1 |
|     3 |401K             |       2 |  

这里是事情变得复杂的地方。我为每个级别的帐户类型创建了表格。例如，支票账户可以是免费支票账户，但免费支票账户可以是学生账户，也可以不是。业务检查可能适用于小型企业或大型企业等。所有账户类型最多有 3 个特定级别。

Account_Level 2 表

+---------+---------+---------+------------------------+
| Id     |    name                 | account_type_id    |                          
|
+---------+---------+---------+-------------------------+
|     1 |        Free Checking     |       1 |                          
|
|     2 |        Business Checking |       1 |

Account_Level 3 表

+---------+---------+---------+------------------------+
| Id     |    name                 | account_lvl2_ID    |                          
|
+---------+---------+---------+-------------------------+
|     1 |        Small Business Checking     |       2 |                          
|
|     2 |        Big Business Checking       |       2 |

请让我知道如何建立我的关系以获得上述预期结果。

Answer 1

您希望有一个从“Account_Level2”表到“branch_accounts”表的外键，它确定 2 级账户是分支账户的子账户。然后，您还需要从“Account_Level3”表到“Account_Level2”表的类似外键，确定每个 3 级帐户与哪个 2 级帐户相关联。

使用此结构，在您的(假定的)其他存储每个成员拥有哪些帐户的表中，该表将具有指向您的帐户级别 3 表的外键，从而提供有关整个帐户层次结构的信息。

有了这个新的层次结构，您将能够像这样执行您选择的查询

SELECT Branch.Id AS branch_id,
       Branch.name AS branch_name,
       account_type,
       Account_Level2.name AS account_level_2_name,
       Account_Level3.name AS account_level_3_name
FROM Branch JOIN branch_accounts 
     ON Branch.Id = branch_accounts.branchId
JOIN Account_Level2 
     ON branch_accounts.id = Account_Level2.account_type_id
JOIN Account_Level3 
     ON Account_Level2.id = Account_Level3.account_lvl2_ID;

快速旁注，尽量与您的命名标准保持一致。使用驼峰式大小写(branch_membership 中的 userId)和蛇形大小写(branch_accounts 中的 account_type)的数据库可能会很困难。您应该尽量使表命名保持一致，并使列命名保持一致。 Here's a helpful link on MySQL "naming conventions" (really just suggestions, but still good to follow) .