How to design a user/role schema in a SQL Server database?
sql-server
Solution
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])
)
Problem
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?