How to create a user invitation model

database-designerd

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!

Best Answer

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

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.

Related Question