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.

Make Accounts possess a foreign answer to Institutions.

Make Users possess a foreign answer to Institutions.

Create a flag around the Users account that signifies that they'll have use of all Accounts on that Institution.

Produce a many-to-many mapping table for Users to Accounts. This is used only when the User doesn't have their flag set such they have use of all Accounts around the Institution.

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.