A better database design on SQL Server?

Question

In SQL Server, I need to design a User table. It should have a UserID (int), a RoleID (int) and a UserName (nvarchar(255)).

A user can be assigned with a name but possibly multiple roles so a UserId may correspond to multiple RoleIDs. This makes it difficult to assign primary key to UserID as UserID may duplicate.

Is there any better design other than breaking it up into multiple tables?

Answer 1

You should have:

1. a user table with UsertId(int), UserName (Varchar)
2. a role table with RoleId(int), RoleName(Varchar)
3. a user_role table with user_id(int), role_id(int)

And don't forget to add the proper indexing and foreign keys.

Answer 2

Ye, have a table Roles, then RolesUsers with UserID and RoleID, and lastly a Users table

edit: where the UserID + RoleID in the RolesUsers are a composite key