creating new relationships with existing hierarchy database

Question

I'm new to working with relational databases especially nested set hierarchies. I have an existing bank hierarchy with bank branches as the "leaves" of the bank. I have hit a wall trying to establish a relationship between a user and a bank as well as a relationship between the same user and the type of account each branch has to offer. Each branch offers different types of accounts and each account has different levels of specification. (Ex. One branch might only have one level of account specification but another could have four levels). I have the tables I created below.

DESIRED RESULT: Ultimately, I just want to be able to run a select statement to retrieve all the accounts a chosen branch has to offer down to it's specification. I would also like to know what account a chosen user has as well as the bank they are connected to.

I don't want to add the account level data to the existing bank hierarchy because I feel that would complicate things even further. (My bank branches could end up having their own leaves in the future). What is it that I am missing below?

(Bank)Heirachy Table:

Top Level -"Global Bank"---> Level 2-"Global Bank-East Coast" && "Global Bank-West Coast"

I created a "Branch" Table with the following data:

 +---------+---------+---------+----------+---------+-----------+
| 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    |                  

I then created a "branch membership" table that contains userId and what branch the user has an account with.

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

I would like to track what type of accounts each branch has so I created a "branch_accounts" table

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

Here is where things get complicated. I created tables for each level of account types. For example a Checking account could be free checking account but a free checking account could either be a student account or not. A Business Checking could be for small businesses or big businesses...etc. All account type have up to 3 specific levels.

Account_Level 2 Tables

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

Account_Level 3 table

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

Please let me know how to establish my relationships to get the desired result above.

Answer 1

You'd want to have a foreign key from your "Account_Level2" table to your "branch_accounts" table, which establishes that a level 2 account is a sub-account of a branch account. You would then also want a similar foreign key from your "Account_Level3" table to your "Account_Level2" table, establishing which level 2 account each level 3 account is associated with.

With this structure, in your (presumed) other table that stores which accounts each member has, that will have a foreign key to your account level 3 table, thus giving information about the whole account hierarchy.

With this new hierarchy, you will be able to perform your selected query like so

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;

Quick side-note, try to remain consistent with your naming standards. It can be tough working with a database where you use camel case (userId in branch_membership) along with snake case (account_type in branch_accounts). You should try to keep table naming consistent and keep column naming consistent. Here's a helpful link on MySQL "naming conventions" (really just suggestions, but still good to follow).