I am developing an application which allows 'users' to create 'lists'. I have a many-to-many relationship between 'users' and 'lists' (this might not be right). Additionally 'Lists' have many 'Tasks'.

What I would like to do is extend this model to include the idea of 'invitations'. I would like users to be able to invite one another to other lists. A user can create many invitations. A user can create an 'invitation' and the invitation would have one or many 'invitees' which, in turn, would be 'Users'. So am confused about how this should be organized in a relational db.

I think the core problem is: users 'own' invitations but also can be the recipient of an invitation. Clear as mud? :)

I was hoping someone could offer some advice on how this might be accomplished. Any example ERDs would be useful. If my question requires further clarification I can provide it.


2 Answers 2


i will recapitulate your description:

  • A user can create lists.
  • A list tackles some tasks.
  • Users can join some lists.
  • A user can send an invitation to some users and propose to join some lists

Based on this description I would propose the following ERD


From this I would deduce the following relations


Primary keys are between apostrophes. Foreign keys can be deduced by the names if the fields (so user_id in lists references user_id in user) The word in parantheses after a foreign_key describes from which relation in the ERD this foreign key is derrived.

I did not care abou the names of the id-columns. Perhaps an id is a surogate key (an artificial key) or some attribue of the entity. also i did not add additional attributes which can be easily added. The characterisation o task as a weak entity is rather arbitrary.

  • This is great. Thanks for the diagram it really helps.
    – Nick
    Aug 17, 2012 at 15:48

You could have a pretty simple schema for this with a "trick": treat all user -> list associations as "invitations", including the ownership relation.

You'd have one table for the users (with say a userid column). Then a table for the lists (with a listid).
The relation between the users and lists tables could be:

userid | listid    | role
bob    | dbas      | owner
alice  | dbas      | invitee
alice  | sysadmins | owner
bob    | sysadmins | invitee
eve    | sysadmins | invitee

An invitation is simply another line in that table.
This allows you to have different levels of access to the lists. A user could "invite" another to have full-control of a list, just read access, or something in between - just define the appropriate set of roles for your use-case.

You might want to add things like "creator" information, last modification information to the lists themselves.

  • squeezing all user list relations in one table seems not to be a good idea to me. You are ignoring all relational design principles (normal forms)
    – miracle173
    Aug 17, 2012 at 15:12
  • I'm not "squeezing all the list relations in one table", I'm creating a table for the one (and only) relation I'm considering (which is "user has access to list" essentially).
    – Mat
    Aug 18, 2012 at 8:45
  • From your statement 'treat all user -> list associations as "invitations"' I concluded that you want to store all user-list relations in one table. I see now that you do not put the 'creator' information in the invitation table (using a "createor" role). It is possible to merge some tables respresenting the relations between two entities into one table that contains a third column that holds a value to discriminate the relations. In contrast to my former statement I think this will not conflict to some normal forms. Nevertheless I do not like such an approach.
    – miracle173
    Aug 21, 2012 at 15:40

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.