How to implement a multi-user account facility in a database

database-designssms

I'm modelling a database schema that has the requirement of including a multi-user account facility for premium customers:

A multiple account enables multiple users to login to the same account
in order to do the job according to their permission level. This means
that it is possible to maintain a single account for your entire
marketing team. The account owner can add as many users as he/she
wants and limit what permissions each of them has. You can choose role
for each user: account manager, marketing executive, marketing
assistant, etc… or create a customized permission.

At the moment I have one users table that stores the information of a generic user with the username and password etc. How would one implement a multi-user account facility? Could an extra field be added to the users table? Would a separate table be required? I would like to use the principle of keeping this simple where possible.

Best Answer

You should have an accounts table and a users table, with either a one-to-many or a many-to-many relationship between the two.

As an example of the many-to-many relationship:

CREATE TABLE Accounts
(
    AccountID int NOT NULL
    , AccountName varchar(30) NOT NULL
);

CREATE TABLE Users
(
    UserID int NOT NULL
    , UserName varchar(30) NOT NULL
);

CREATE TABLE AccountsUsers
(
    AccountID int NOT NULL
        FOREIGN KEY
        REFERENCES Accounts(AccountID)
    , UserID int NOT NULL
        FOREIGN KEY
        REFERENCES Users(UserID)
    , UserLevel int NOT NULL
);

The UserLevel represents the type of account access; account manager, marketing executive, marketing assistant, etc.

The one-to-many relationship would look like:

CREATE TABLE Accounts
(
    AccountID int NOT NULL
        CONSTRAINT PK_Accounts
        PRIMARY KEY CLUSTERED
    , AccountName varchar(30) NOT NULL
);

CREATE TABLE UserLevels
(
    UserLevelID int NOT NULL
        CONSTRAINT PK_UserLevels
        PRIMARY KEY CLUSTERED
    , UserLevelDesc varchar(30) NOT NULL
);

CREATE TABLE Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , UserName varchar(30) NOT NULL
    , AccountID int NOT NULL
        CONSTRAINT FK_Users_AccountID
        FOREIGN KEY 
        REFERENCES Accounts(AccountID)
    , UserLevelID int NOT NULL
        CONSTRAINT FK_Users_UserLevelID
        FOREIGN KEY
        REFERENCES dbo.UserLevels(UserLevelID)
);

Insert some sample data:

INSERT INTO dbo.Accounts (AccountID, AccountName)
VALUES (1, 'Microsoft');

INSERT INTO dbo.UserLevels (UserLevelID, UserLevelDesc)
VALUES (1, 'User')
    , (2, 'Manager')
    , (3, 'Owner');

INSERT INTO dbo.Users (UserID, UserName, AccountID, UserLevelID)
VALUES (1, 'Bill Gates', 1, 3)
    , (2, 'Sataya Nadella', 1, 2);

Query combining the three tables:

SELECT a.AccountName
    , u.UserName
    , ul.UserLevelDesc
FROM dbo.Accounts a
    INNER JOIN dbo.Users u ON a.AccountID = u.AccountID
    INNER JOIN dbo.UserLevels ul ON u.UserLevelID = ul.UserLevelID

Results:

╔═════════════╦════════════════╦═══════════════╗
║ AccountName ║    UserName    ║ UserLevelDesc ║
╠═════════════╬════════════════╬═══════════════╣
║ Microsoft   ║ Bill Gates     ║ Owner         ║
║ Microsoft   ║ Sataya Nadella ║ Manager       ║
╚═════════════╩════════════════╩═══════════════╝

Cleanup: 

IF OBJECT_ID(N'dbo.UserLevels', N'U') IS NOT NULL
DROP TABLE dbo.UserLevels;
IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;
IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
DROP TABLE dbo.Accounts;
Related Question