Assigning multiple roles to a single user

Question

I am currently designing a database with a role driven menu which has following requirements.

 1. Menu items can be assigned to a certain 'role'
 2. A role will be assigned to a user
 3. A user can have multiple roles

I have designed the database however I cannot map multiple roles to a single user in my current design. Also, I don't understand how would I map the duplicate menu items (When a user is assigned multiple roles) while querying even if I include it in my design somehow.

I though about storing roles as comma separated values however that would further introduce complications. Is their a better solution?

Answer 1

C. Trimble is right (+1). If your menu items are available to multiple roles and your users can have multiple roles, then you actually have two many-to-many relationships that need to be recorded.

That means that you need to add one more table, as below:

This table could have columns as per C. Trimble's answer, or you could have a compound primary key of just UserId + RoleId - analogous to your MenuItemRoles table. The latter would be my design preference since the table is a pure intersection and there is no particular reason to expect that UserRoles would have children relating to it.

Note that when you are retrieving user menu item access you can short-circuit the link from UserRoles to MenuItemRoles because they both have a RoleId column. This means you can join directly between the intersection tables, leaving Roles out of it. In TSQL it would look like:

select I.*   -- Never select * in the real world.
from MenuItem I
  inner join MenuItemRoles IR
  on I.ItemId = IR.ItemId
  inner join UserRoles UR
  on IR.RoleId = UR.RoleId
where
  UR.UserId = @TheUserImLookingFor

You could do the same thing in LINQ. If you're using EF then you're just going to have to go the long way around (through Roles).