For accomodating the
Edit: Columns w/ the same name in different tables imply a Foreign Key. 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: Why would you use a lookup table (and another join! No sense in a lookup for a one-to-many relationship, just standard 3NF.
A hobo has sneaked in unobserved, and is milking one of the cows for his lunch, the patient animal standing quietly while the tramp ...
See full summary » A group of cows standing in the corner of a pasture lot.
A hobo has sneaked in unobserved, and is milking one of the cows for his lunch, the patient animal standing quietly while the tramp does his work.
Just curious what is the best practice for solving the following issue. 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.Can a user have access to (be associated with) the accounts of more than one institution?If Yes, would a "super-user" have access to all the accounts of one institution, all the accounts of a set of institutions, or all the accounts of all institutions?(If no, then we'll want to factor in restrictions on account assignments.)Institutions Institution ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY Name VARCHAR(255) Users User ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY Username VARCHAR(255) NOT NULL Institution ID INT NOT NULL Accounts Account ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY Account VARCHAR(255) NOT NULL Institution ID INT NOT NULL Users_Accounts Users_Accounts ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY User ID INT NOT NULL Account ID INT NULL table have global (*) access.This way, you can determine the Institution on any Account and/or User, as well as their permissions.