How do I create a user invitation model?

Question

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!

Answer 1

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

user('user_id')
list('list_id',user_id(creates))
joins('list_id','user_id')
task('list_id','task_id')
invitation('invitation_id',user_id(sends))
receives('invitation_id','user_id')
proposes('invitation_id','list_id')

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.

Answer 2

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.