Database schema pattern for grouping records

Question

I'm looking for a design pattern to manage records in a relational database which belong together, e.g. grouping multiple contacts. The group itself does not need to be an entity. A group should have an unlimited count of members.

It should be possible to run a query to get other group members of a given record.

My idea is to manage it in one table:

GROUPINGS
  integer group
  integer member_id

primary_key (group, member_id)
foreign_key (member_id)

EDIT: Beware that group is not a foreign key. It's just a unique identifier. It should be increased for every member group which is built.

Here is an example content:

GROUPINGS group | member_id
          -----------------
              1 | 10
              1 | 11
              1 | 12
              2 | 20
              2 | 21
              3 | 10
              3 | 40

This example contains three groups: (10,11,12) and (20,21) and (10,40). You see that 10 is included in two groups.

To query the "neighbors" of member 10 we can use this SQL statement:

SELECT g2.member_id
FROM groupings g1
JOIN groupings g2 ON g1.group      = g2.group 
                 AND g1.member_id != g2.member_id
WHERE g1.member_id = 10

=> 11,12,40

What do you think? Perhaps this is a known pattern - are there links to find more about this?

EDIT: Renamed table "groups" to "groupings" and renamed attribute "group_id" to "group" to make it obvious that a record in this table is not a group - it's a link between a group and a member. Group is not an entity.

@Chris: Yes, this is possible. I have enhanced the example to demonstrate it. — Georg Ledermann, Nov 29 '11 at 07:47

score 2 · Answer 1 · answered Nov 29 '11 at 12:53

2

enter image description here

answered Nov 29 '11 at 12:53

Damir Sudarevic

21,891
3
47
71

There is no group entity in my schema - and it should not be required. Your schema is a solution for a different problem. – Georg Ledermann Nov 29 '11 at 14:09

score 1 · Answer 2 · answered Nov 28 '11 at 20:51

1

What you have outlined is a pretty standard solution, a relational table between two entities - Group and Member. I am sure there are alternatives, but this is the solution I would go with.

answered Nov 28 '11 at 20:51

Maess

4,118
20
29

My answer assumes that you also have a group and a member table. – Maess Nov 28 '11 at 20:53
Because group_id is not a foreign key this is some kind of of special solution. There is no group table. I will rename the table in the original post to avoid this misunderstanding. – Georg Ledermann Nov 29 '11 at 07:20

score 1 · Answer 3 · edited Nov 28 '11 at 20:54

1

Looks fine to me - is a normal solution to end at if a member can be part of multiple groups, which presumably they can.

The only suggestion I'd make is with your SQL query - I'd use a JOIN instead, but that's nothing to do with your schema:

SELECT g2.member_id
FROM groups g1
    INNER JOIN groups g2 ON g1.group_id = g2.group_id AND g1.member_id <> g2.member_id
WHERE g1.member_id = 10

edited Nov 28 '11 at 20:54

Ian Nelson

57,123
20
76
103

answered Nov 28 '11 at 20:53

AdaTheDev

142,592
28
206
200

You are right, a JOIN is nicer to read, but it's just syntactic sugar for most db's. I have edited my post above. And yes, a member can be part of multiple groups. – Georg Ledermann Nov 29 '11 at 07:51

Database schema pattern for grouping records

3 Answers3

Linked