3

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.

3

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.

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.

  • I am inviting users to join a 'list'. Where a list is another table. users can be assigned to many lists. Would it be wise to include a FK to list in InvitationUsers? – Nick Aug 6 '11 at 22:17
  • Does a list have anything to do with an invitation? – Aaron Bertrand Aug 7 '11 at 1:23
  • Yes, the invitation is an invitation to join a list – Nick Aug 7 '11 at 1:45
  • Then wouldn't ListID just be a property of an Invitation ("other details about the invitations"), with an FK to Lists? Why would you need to repeat the ListID over and over again for every single user? – Aaron Bertrand Aug 7 '11 at 1:52
  • You're right. That is actually what I have. I am creating a model for my data in code. I trying to define the entity relationships using an ORM called Doctrine. Feels like juggling :) Thanks a lot for the help. – Nick Aug 7 '11 at 14:15
1

You're heading in the right direction but I'd use different terms. An invite is a request to attend an event of some sort, so more appropriate naming would be:

Event (EventId, OwnerUserId)

Invite (EventId, UserId)

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.