Should I have relationships coming off my join table or the two tables it's joining?

user_14172628 0
Viewed 47

Currently I have a many to many relationship between users & events via a join table named participants.

user         event         participant
========     ========      =============
id           id            user_id
name         title         event_id
             location

I want to add another table represneting a users transactions while at an event but I'm unsure if I should reference the join table or the two separate tables.

I think the two options are either this:

user         event         participant         transaction
========     ========      =============       ============
id           id            user_id             id
name         title         event_id            amount
             location                          user_id
                                               event_id

Or this:

user         event         participant         transaction
========     ========      =============       ============
id           id            user_id             id
name         title         event_id            amount
             location                          participant_id
database-design postgresql sql
3 Answers

Interesting question. Some of the answer is a subjective question: Is the transaction move associated with a period participating in an event? Or is the transaction more associated with a person and an event, and less with the participation?

Your data model doesn't specify if a person can participate in an event more than once (say at different times). If so, then the transaction is probably tied to the participation.

On the other hand, if the transaction could be any time after someone participates (the participation is a prerequisite), then it might be more tied to the person and event, then to the actual participation.

Two points to make. Under many circumstances, it doesn't really make a difference. If you are more likely to want to know the person and event, then include those. If you are more likely to want to know about the participation (say the date/time of that), then use that.

Second, it might make a difference in some cases based on the details of what you want to model. Your question doesn't provide enough information to suggest whether that is true one way or the other.

user_11440350

Option 1 should remove the participant table if your UI doesn't show info about participants. If you need participants, option 2 is better.

user_85461280

How is the value of participant_id determined when the primary key of the participant table is (user_id, event_id)?

Furthermore, you don't want to allow a transaction to be registered for a user at an event they don't participate in. In other words, if the record for (user A, event X) does not exist in the participant table, you as well do not want any trascations to be recorded for user A at event X.

This means that by using option (1) and using a primary key of (transcation_id, user_id, event_id) you would both have a more natural primary key, and be able to reference the participant table by (user_id, event_id) for ensuring your data integrity.

Also, as mentioned by others, a more natural name for your participant table can be event_users.

user_46211880
Related