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;