Tables that reference each other

Question

I have 2 tables with a circular dependency; each group has a leading member

+---------+-------+----------+
| GroupId | Group | MemberId |
+---------+-------+----------+
|       1 | g1    |        1 |
+---------+-------+----------+

and each member belongs to a group:

+----------+--------+---------+
| MemberId | Member | GroupId |
+----------+--------+---------+
|        1 | m1     |       1 |
+----------+--------+---------+

One possible solution is:

+----------+--------+---------+---------+
| MemberId | Member | GroupId | Leading |
+----------+--------+---------+---------+
|        1 | m1     |       1 |       1 |
+----------+--------+---------+---------+

but leads to denormalization since the member to leader ratio is high.

What is the best way to design this?

score 1 · Accepted Answer · answered Feb 19 '15 at 20:33

The two tables are correctly designed. You have all information:

1. groupID,GroupName,LeaderMemberID; 
2. MemberID,MemberName,MemberGroupID;

The problem might arise, if you have multiple Leaders for the same one group, and/or each Member might belong to multiple groups.

But you can easily change the design to the following often used approach:

1. groupID,GroupName; 
2. MemberID,MemberName;
3. MemberID,GroupID,isLeader;

I hope it helps.

score 0 · Answer 2 · answered Feb 19 '15 at 21:16

Assuming each group can have only one leader, I'd say your first example is fine. Though I would call the leader field "LeadMemberId" or some such rather than just "MemberId", as someone else looking at the table might be seriously confused about what it means to have a single member id in the group record.

If a group can have multiple leaders, than I'd add an "IsLeader" flag to the member record. The fact that most records will have false or 0 is irrelevant. If there can be only one, though, I would avoid doing this, as the data structure implies that there can be more than one, and someone could make a mistake and set the flag true in more than one record and this could cause you grief.

Tables that reference each other

2 Answers2