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.

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!

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
    
This is great. Thanks for the diagram it really helps. –  Nick Aug 17 '12 at 15:48
add comment

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.

share|improve this answer
    
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 '12 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 '12 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 '12 at 15:40
add comment

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.