How to use ManyToMany relations to track user membership?

Question

What kind of relations is required to store user's membership in multiple groups to be able to recover:

1. history of user participation in some groups (date joined, date quit)
2. list of current user groups (in join order) to determine his current status
3. list of users, who were participating in that group at a given period of time

I guess it is ManyToMany (or an ugly kind of OneToMany), but can't figure out how to use it; need a minimal example, preferably - for Django's models.

Also, which consistency problems are expected when some group/user needs to be deleted?

Answer 1

• members
• groups
• groupmembers

Groupmembers is your join table and has such things as:

• member (one member has many groupmember records)
• group (one group has many groupmember records)
• create date
• remove date (leave null until applicable)

So for your requirements:

1. query groupmembers with a group and sort by date
2. query groupmembers with a member (sort by create date)
3. query groupmembers with a group and remove is null (or inside a date range)