Just curious what's the best practice for fixing the next problem; I have institutions, accounts and customers tables. Institutions and accounts possess a one-to-many relationship. Institutions and customers possess a one-to-many relationship. Customers and accounts possess a many-to-many relationship or else they will have a many-to-all (or *) accounts. Meaning, that the user might be designated to any or all accounts, and then any new accounts which are put into the institution the consumer would get access to without clearly adding the connection.
I'd get it done like so:
Institutions InstitutionID INT IDENTITY(1,1) NOT NULL PRIMARY KEY Name VARCHAR(255) Users UserID INT IDENTITY(1,1) NOT NULL PRIMARY KEY Username VARCHAR(255) NOT NULL InstitutionID INT NOT NULL Accounts AccountID INT IDENTITY(1,1) NOT NULL PRIMARY KEY Account VARCHAR(255) NOT NULL InstitutionID INT NOT NULL Users_Accounts Users_AccountsID INT IDENTITY(1,1) NOT NULL PRIMARY KEY UserID INT NOT NULL AccountID INT NULL
Possess a UserID w/ a
NULL entry within the
Users_Accounts table have global (*) access. By doing this, you are able to determine the Institution on any Account and/or User, in addition to their permissions.
Edit: Posts w/ exactly the same title in various tables imply an overseas Key. Please rely on them discover doing heavy loads.
Accounts possess a foreign answer to
Users possess a foreign answer to
Create a flag around the
Users account that signifies that they'll have use of all
Accounts on that
Produce a many-to-many mapping table for
Accounts. This is used only when the
User doesn't have their flag set such they have use of all
Accounts around the
This will solve your condition.
Make use of a mapping table for a lot of-to-many associations. If your user has numerous accounts, produce a customers_to_account mapping table with two posts, one using the account foreign key, and something with customers foreign key, and that i would even make sure they are together an amalgamated primary key:
customers account ----- ---------- 1 5 1 10 2 5 2 10
So, accounts 5 and 10 both map to customers 1 and a pair of.