snippetsqlMinor
How can I model this relationship?
Viewed 0 times
thiscanhowmodelrelationship
Problem
My database design skills are a bit rusty so I was hoping to get some help from y'all =)
I have a 'user' table that contains all the typical fields (id,name,email,etc.). Users can have many 'invitations'. (many to one) Each 'invitation' has a single owner (user). However, a single 'invitation' can have many 'invitees'.
My confusion is this: If users can have many invitations and invitations can only have one owner (user) but that single invitation can contain many invitees (these are users) how should this be modeled? Do I need a new table?? ('invite')
I hope my question makes sense.
I have a 'user' table that contains all the typical fields (id,name,email,etc.). Users can have many 'invitations'. (many to one) Each 'invitation' has a single owner (user). However, a single 'invitation' can have many 'invitees'.
My confusion is this: If users can have many invitations and invitations can only have one owner (user) but that single invitation can contain many invitees (these are users) how should this be modeled? Do I need a new table?? ('invite')
I hope my question makes sense.
Solution
If an invitations is just an invitation, then it seems simplest to store all of the users for an invite in their own row in a separate table.
A lot of people are tempted to store all of the users of an invitation in a comma-separated list or as XML. Resist this temptation; it leads to nothing but trouble.
If there is only a single type of invitation (like "be my friend on Facebook and we can hug and all that") then a simpler model might be:
If an owner can send an invitation to various things (a dating service, a boat show and a movie) then we'll need more details about what those relevant things are.
Users
- UserID, PK
- other details about the users
Invitations
- InvitationID, PK
- OwnerID or OwningUserID or just UserID, FK to Users
- other details about the invitations
InvitationUsers
- InvitationID, FK to Invitations
- UserID FK to Users
[PK on (InvitationID, UserID)]A lot of people are tempted to store all of the users of an invitation in a comma-separated list or as XML. Resist this temptation; it leads to nothing but trouble.
If there is only a single type of invitation (like "be my friend on Facebook and we can hug and all that") then a simpler model might be:
Users
- UserID, PK
- other details about the users
Invites
- SenderID, FK to Users(UserID)
- RecipientID, FK to Users(UserID)
[PK on (SenderID, RecipientID)]If an owner can send an invitation to various things (a dating service, a boat show and a movie) then we'll need more details about what those relevant things are.
Code Snippets
Users
- UserID, PK
- other details about the users
Invitations
- InvitationID, PK
- OwnerID or OwningUserID or just UserID, FK to Users
- other details about the invitations
InvitationUsers
- InvitationID, FK to Invitations
- UserID FK to Users
[PK on (InvitationID, UserID)]Users
- UserID, PK
- other details about the users
Invites
- SenderID, FK to Users(UserID)
- RecipientID, FK to Users(UserID)
[PK on (SenderID, RecipientID)]Context
StackExchange Database Administrators Q#4418, answer score: 3
Revisions (0)
No revisions yet.