2

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.

1 Answer 1

4

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.