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:
Less data used up by an auto incrementing id for members of groups
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:
groups table example:
groups_users table example:
|id (only if referencing members of group rather than users directly)||user_id||group_id|
events table example:
events_users table example:
|user_id / group_member_id||event_id|
|1002 / 3003||4001|
|1001 / 3001||4002|