Plump Worm Plump Worm - 1 month ago 5
SQL Question

SQL Server - DB Design - Data Integrity - Enforce attribute value W/O trigger

I am fairly new to SQL Server, so apologies if my question is dramatically dumb.

Assume the following business rules.


  1. User
    can be a member of 0..*
    Teams
    and as such is called
    Team_Member

  2. Team_Member
    cand hold 0..*
    Roles
    within the team

  3. Role_in_Team
    can have 0..*
    Deputies



Snapshot of tables:

Teams_Members:


  • id_team_member (PK - surrogate)

  • team_name (FK - Teams)

  • team_member (FK - Users)



Teams_Members_Roles:


  • id_team_member (FK - Teams_Members)

  • role (FK - Roles)

  • deputy (FK - Users) or (FK - Teams_Members)`



Can you think of any way of enforcing a rule that a deputy is a member of the same team (in other words - that a person from a different team is not assigned as a deputy of a role within that team) other than via a trigger?

Answer

Here is the logical design. If you insist on integer PKs for all tables, you cold add them to each table, but still have to keep composite keys (UNIQUE) and reference them in foreign keys.


| User USER_ID exists.

User {USER_ID}
 KEY {USER_ID}


| Team TEAM_ID exists.

Team {TEAM_ID}
 KEY {TEAM_ID}


| Role ROLE_ID exists.

Role {ROLE_ID}
 KEY {ROLE_ID}


| User USER_ID is member of Team TEAM_ID.

For each user, that user may be member of more than one team; for each team, more than one user may be member of that team.

If a user is member of a team than that user must exist, and that team must exist.

TeamMember {USER_ID, TEAM_ID}
       KEY {USER_ID, TEAM_ID}

FOREIGN KEY {USER_ID} REFERENCES User {USER_ID}
FOREIGN KEY {TEAM_ID} REFERENCES Team {TEAM_ID}


| User USER_ID holds role ROLE_ID in team TEAM_ID.

For each user and team, that user can hold more than one role in that team.

For each user and role, that user may hold that role in more than one team.

For each role and team that role may be held by more than one user i that team.

If a user holds a role in a team than that user is member of that team.

If a user holds a role in a team than that role must exist.

TeamMemberRole {USER_ID, TEAM_ID, ROLE_ID}
           KEY {USER_ID, TEAM_ID, ROLE_ID}

FOREIGN KEY {USER_ID, TEAM_ID} REFERENCES TeamMember {USER_ID, TEAM_ID}
FOREIGN KEY {ROLE_ID}          REFERENCES Role {ROLE_ID}


| User PRIMARY_ID holding role ROLE_ID, as primary user, in team TEAM_ID is assigned deputy DEPUTY_ID for that role.

Each user holding a role, as primary user, in a team may be assigned more than one deputy for that role in that team.

Each member of a team may be assigned as deputy to more than one primary user for a role in that team.

If a user in a team is assigned a deputy for a role, than that user holds that role in that team.

If a deputy is assigned to a user holding a role in a team, than that deputy is a member of that team.

It is not possible that a user is deputy to himself.

TeamDeputy {PRIMARY_ID, TEAM_ID, ROLE_ID, DEPUTY_ID}
       KEY {PRIMARY_ID, TEAM_ID, ROLE_ID, DEPUTY_ID}

FOREIGN KEY {PRIMARY_ID, TEAM_ID, ROLE_ID} REFERENCES TeamMemberRole {USER_ID, TEAM_ID, ROLE_ID}
FOREIGN KEY {DEPUTY_ID, TEAM_ID} REFERENCES TeamMember {USER_ID, TEAM_ID}

CHECK PRIMARY_ID <> DEPUTY_ID

team_member_deputy

Comments