StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatabase schema pattern for grouping records
text
Body
copied!<p>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.</p> <p>It should be possible to run a query to get other group members of a given record.</p> <p>My idea is to manage it in one table:</p> <pre><code>GROUPINGS integer group integer member_id primary_key (group, member_id) foreign_key (member_id) </code></pre> <p><strong>EDIT:</strong> Beware that <code>group</code> is <strong>not</strong> a foreign key. It's just a unique identifier. It should be increased for every member group which is built.</p> <p>Here is an example content:</p> <pre><code>GROUPINGS group | member_id ----------------- 1 | 10 1 | 11 1 | 12 2 | 20 2 | 21 3 | 10 3 | 40 </code></pre> <p>This example contains three groups: (10,11,12) and (20,21) and (10,40). You see that 10 is included in two groups.</p> <p>To query the "neighbors" of member 10 we can use this SQL statement:</p> <pre><code>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 </code></pre> <p>What do you think? Perhaps this is a known pattern - are there links to find more about this?</p> <p><strong>EDIT:</strong> 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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload