1

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
0

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.