CODEWITHSUNDEEP
sqlrelational-databaserelationship
user931288
user931288

Reputation: 21

one to one within a many to many relationship

I'd like to assign a primary role to a user with many roles and I'm wondering what method is preferable, if any. Either to make a primary_role_id column in the users table or add an is_primary column in the pivot table:

Users:

id name primary_role_id here?
1 alice 2
2 bob 1

Roles:

id name
1 super
2 admin

Role User:

user_id role_id OR is_primary here?
1 1 true
1 2 false
2 1 false
2 2 true

Upvotes: 0

Views: 305

Answers (1)

PerformanceDBA
PerformanceDBA

Reputation: 33708

Requirement

It is simple, but not quite that simple. What you really want is:

  • each User is permitted 1-to-n Roles
    • of that set, one RoleUser is primary

Therefore:

  • the IsPrimary indicator has to be in RoleUser
  • you need a CHECK Constraint on RoleUser, that calls a Function, to ensure that there is just 1 IsPrimary Role per User.
  • That is an ordinary capability in Standard SQL, available in genuine SQL Platforms, not possible in the freeware.

Requirement is Not

  • each User is permitted 1-to-n Roles
  • each User has 1 primary Role
    • which leads to maintenance complexity: the set is in one place, the indicator for the set in another. The Function required checks that the User has the Role in RoleUser that is declared as PrimaryRoleId.
    • which is inferior and incorrect
    • the "User has one-to-one PrimaryRole" is true as a result, but not as a declarative.

Upvotes: 2

Related Questions