How to design a user/role schema in a SQL Server database?
Not quite sure what you mean, but...
User_Roles
should have 2 columns onlyUser_id
andRole_id
Both of these form the Primary Key- You do not need an extra id column
User_Roles
User_id
is a foreign key toUsers.id
Role_id
is a foreign key toRoles.id
Edit: Now I understand. Yes, always use foreign keys
Also...
- if
password
isnvarchar(50)
, this implies plain text. This is bad. - if you have duplicate
name
values inUsers
, 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])
)