Should a column referencing a group member have its foreign key directly point to the user or add an id to M2M table which represents users in a group



  • I'm creating a many-to-many table in a database which contains a column referencing members of a group as well as a column referencing an event.

    I'm questioning whether I should set the column referencing the group member to reference the main user entry in the users table or instead add a primary key to a groups_users many-to-many table and reference the user_id there so that when the user is removed from the group then they are automatically removed from the event with ON DELETE CASCADE instead of having to manually remove them with a seperate query.

    From what I know, the pros and cons for directly referencing the user are as such:

    Pros:

    Less data used up by an auto incrementing id for members of groups
    

    Cons:

    Have to manually remove users from events with a seperate query
    

    The pros and cons for the other approach are a reverse of the first approach.

    users table example:

    id username
    1001 User 1
    1002 User 2

    groups table example:

    id name owner_id
    2001 Group 1 1001
    2002 Group 2 1001

    groups_users table example:

    id (only if referencing members of group rather than users directly) user_id group_id
    3001 1001 2001
    3002 1001 2002
    3003 1002 2001

    events table example:

    id group_id name
    4001 2001 Event 1
    4002 2002 Event 2

    events_users table example:

    user_id / group_member_id event_id
    1002 / 3003 4001
    1001 / 3001 4002


  • First, I would not add an artificial primary key id to the groups_users table. The natural key consisting of (group_id, user_id) is perfectly fine as primary key.

    Concerning the question whether events_users should have a foreign key to users or to groups_users, ask yourself the question: is it the group membership of a user or the user itself that is related to an event? For example, if I am in two groups, could I be related to an event twice? The answer to these questions will tell you how to model this. From my point of view, a the foreign key should be to the user, but then you know the situation better.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2