-1

I have the following tables (among others):

  1. Users
  2. Departments
  3. Roles
  4. UserRoles

The UserRoles table has the following fields:

  1. UserId
  2. RoleId
  3. DepartmentId (NULL)

The idea here is I can give a user a specific role, for specific department(s), if the DepartmentId is NULL, then this role applies to all departments.

The problem here I cannot make a composite primary key out of the three fields in the UserRoles table since the DepartmentId is nullable.

Should I just go with a unique index without a primary key since I cannot have a primary key for the first two columns only (that will prevent the user from having the same role to multiple departments), or should I change the whole structure to something else (any ideas will be appreciated).

Him
  • 373
  • 1
  • 2
  • 15

1 Answers1

0

I would make DepartmentID not null and assign a value of 0 to symbolize all departments. This will help you both in creating a PK and in queries where now you can simply say where DepartmentID = 0 instead of where DepartmentID is null.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22