How do I create a user invitation model?

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.

Thanks!

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.

StackExchange Database Administrators Q#22660, answer score: 2