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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
5
  • 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, 2011 at 22:17
  • Does a list have anything to do with an invitation?
    – Aaron Bertrand
    Aug 7, 2011 at 1:23
  • Yes, the invitation is an invitation to join a list
    – Nick
    Aug 7, 2011 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, 2011 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, 2011 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)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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