Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Awesome.. Thanks for this! –  Nick Aug 6 '11 at 22:05
    
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 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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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