jeudi 2 août 2018

Group membership pattern

I am trying to figure out how to implement a group membership module for an app where the natural key for the group is its set of users. Stated another way, any combination of users can only create a single group.

By way of example, let's say we have three users: UserA, UserB, and UserC. Now let's say UserA starts a group with UserB. We'll call it Group1. If UserA attempts to start another group with UserB or UserB attempts to create a group with UserA, Group1 will be returned. UserA, however, can still start a group with UserB and UserC since we do not yet have a group with that combination of users. We'll call it Group2.

So the natural key for Group1 is UserA + UserB.

The natural key for Group2 is UserA + UserB + UserC.

Hopefully that makes sense.

The table contents for the above example would be:

User
=================
user_id  username
      1  UserA
      2  UserB
      3  UserC

Group
=========================
group_id  name    alt_key
       1  Group1  1,2
       2  Group2  1,2,3

Member
============================
member_id  group_id  user_id
        1         1        1
        2         1        2
        3         2        1
        4         2        2
        5         2        3

alt_key is a sorted list of user_ids.

Clearly this method is not very scalable so I improved it a bit by hashing the alt_key. This made the key significantly smaller for larger groups, but since hashes aren't unique, it still requires lots of gymnastics to ensure that users cannot create duplicate groups.

Anyhow, I'm wondering if there's a pattern or better implementation that allows for an "unlimited" number of users per group without crazy queries to ensure group membership is unique.

Aucun commentaire:

Enregistrer un commentaire