How to design a user/role schema in a SQL Server database?

Question

I want to design a user/role system:

The users have a name and a password and then the user can have several roles like Admin.

For this I created a schema like this:

Users:

CREATE TABLE [dbo].[Users]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,
    [password] [nvarchar](50) NULL,

    CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([id] ASC)
)

Roles:

CREATE TABLE [dbo].[Roles]
(
    [id] [int] NOT NULL,
    [name] [nvarchar](50) NULL,

    CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

user_roles:

CREATE TABLE [dbo].[User_Roles]
(
    [id] [int] NOT NULL,
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([id] ASC)
)

My question is: should I use foreign keys User_Roles.User_id -> User.Id

If yes why?

**YES!** Of course you should have foreign keys. Why, you ask?? FK help ensure **data consistency** - and without FK, you can store any kind of `id` into your `user_roles` table - without checking whether those refer to valid users and/or roles - not something you want to do (and not something you want to have to clean up later on!) — marc_s, Jun 04 '12 at 10:25

score 12 · Accepted Answer · edited May 23 '17 at 12:08

12

Not quite sure what you mean, but...

User_Roles should have 2 columns only User_id and Role_id
Both of these form the Primary Key
You do not need an extra id column User_Roles
User_id is a foreign key to Users.id
Role_id is a foreign key to Roles.id

Edit: Now I understand. Yes, always use foreign keys

Also...

if password is nvarchar(50), this implies plain text. This is bad.
if you have duplicate name values in Users, how do you know which user is which?
Especially if they have the same password (which will happen because we meatsacks are stupid)

Edit after comment after primary key creation...

CREATE TABLE [dbo].[User_Roles]
(
    [User_id] [int] NOT NULL,
    [Role_id] [int] NOT NULL,

    CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED ([User_id], [Role_id]),
    CONSTRAINT [UQ_ReversePK] UNIQUE ([Role_id], [User_id])
)

edited May 23 '17 at 12:08

Community

1
1

answered Jun 04 '12 at 09:41

gbn

422,506
82
585
676

password and username is just for the demo, its not plain text. my focus is on the role system – gurehbgui Jun 04 '12 at 09:46
how to set the primary key in user_roles? – gurehbgui Jun 04 '12 at 13:29
Why use a [`User_Roles` association table](https://stackoverflow.com/questions/4714607/how-to-properly-index-a-many-many-association-table) in the first place? Is the *existence* of a `Users.id` as a foreign key in the `Roles` table not enough? – Jens May 30 '17 at 01:51
@Jens: users can be in multiple roles, roles can have multiple users? Your suggestion would mean "one user only per role" – gbn May 31 '17 at 12:37

score 1 · Answer 2 · answered May 18 '15 at 20:00

Spring Security makes this recommendation:

create table users(
    username varchar_ignorecase(50) not null primary key,
    password varchar_ignorecase(50) not null,
    enabled boolean not null
);

create table authorities (
    username varchar_ignorecase(50) not null,
    authority varchar_ignorecase(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);
create unique index ix_auth_username on authorities (username,authority);

score 0 · Answer 3 · answered Jun 04 '12 at 11:34

Always use foreign keys when the data models a relation. In your sample, if you don't create the foreign keys, there is nothing preventing you (or someone else with access to the database) from mistakenly (or deliberately) deleting a role which is currently used.

Let's say you have many users and a few roles. One of the roles is called 'Admin' and is required in you application in order to do some tasks. If you don't have the foreign keys setup, there is nothing in the database to prevent someone from deleting the admin role, casuing your application to:

Probably crash since it will look for a role which is no longer in the database
If not the above, then at least no users will have the 'Admin' role, closing down the parts of the application where it is required

If, on the othe hand you HAVE setup the foreign keys, you will receive an error from the database if you try to delete a role which is currently assigned to some user (through the User_Roles table).

score 0 · Answer 4 · answered Mar 25 '18 at 07:34

One of the best approach which is slightly different from @GBN as well will be:

I hope this helps you or someone else

CREATE TABLE [dbo].[UserRoles](
    [roleId] [int] NOT NULL,
    [userId] [int] NOT NULL,
    [CreateDate] [datetime] NULL,
    [CreateUser] [nvarchar](30) NULL,
    [ModifyDate] [datetime] NULL,
    [ModifyUser] [nvarchar](30) NULL,
 CONSTRAINT [PK_User_Roles] PRIMARY KEY CLUSTERED 
(
    [roleId] ASC,
    [userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_ReversePK] UNIQUE NONCLUSTERED 
(
    [roleId] ASC,
    [userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserRoles] ADD  CONSTRAINT [DF_UserRoles_ModifyDate]  DEFAULT (getdate()) FOR [ModifyDate]
GO

ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_roleId] FOREIGN KEY([roleId])
REFERENCES [dbo].[Roles] ([roleId])
GO

ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_roleId]
GO

ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD  CONSTRAINT [FK_UserRoles_userId] FOREIGN KEY([userId])
REFERENCES [dbo].[Users] ([uId])
GO

ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_userId]
GO

score 0 · Answer 5 · answered Aug 14 '19 at 09:13

The users_roles table should contain the mapping between each user and their roles. Each user can have many roles, and each role can have many users:

TABLE users
  id INTEGER NOT NULL PRIMARY KEY,
  userName VARCHAR(50) NOT NULL

TABLE roles
  id INTEGER NOT NULL PRIMARY KEY,
  role VARCHAR(20) NOT NULL

CREATE TABLE users_roles (
  userId INTEGER NOT NULL,
  roleId INTEGER NOT NULL,
  primary key (userId, roleId),
  foreign key (userId) references users(id),
  foreign key (roleId) references roles(id)
);

How to design a user/role schema in a SQL Server database?

5 Answers5

Linked