What is the best practice for accomodating both a one-to-many and a one-to-all (or one-to-*) relationship?

Question

Just curious what is the best practice for solving the following issue. I have institutions, accounts and users tables. Institutions and accounts have a one-to-many relationship. Institutions and users have a one-to-many relationship. Users and accounts have a many-to-many relationship or alternatively they could have a many-to-all (or *) accounts. Meaning, that a user could be designated to all accounts, and any new accounts that are added to the institution the user would have access to without explicitly adding the relationship.

Answer 1

Use a mapping table for many-to-many relationships. If a user has many accounts, create a users_to_account mapping table with two columns, one with the account foreign key, and one with users foreign key, and I would even make them together a composite primary key:

users           account
-----           ----------
1               5
1               10
2               5
2               10

So, accounts 5 and 10 both map to users 1 and 2.

Answer 2

I'd do it 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

Have a UserID w/ a NULL entry in the Users_Accounts table have global (*) access. This way, you can determine the Institution on any Account and/or User, as well as their permissions.

Edit: Columns w/ the same name in different tables imply a Foreign Key. Please use them if you aren't doing heavy loads.

Answer 3

Make Accounts have a foreign key to Institutions.

Make Users have a foreign key to Institutions.

Make a flag on the Users account that indicates that they will have access to all Accounts on that Institution.

Create a many-to-many mapping table for Users to Accounts. This will be used only if the User does not have their flag set such that they have access to all Accounts on the Institution.

This should solve your problem.