Search code examples
databaserelational-databaseentity-relationship

1 to 1 and 0 to 1

How would you model the relational data model for the following case?

leader is chosen to lead group
a group is led by one and only one leader
a leader leads 0 to 1 group

This is my solution

Group [GroupID(PK)]
Leader[LeaderID(PK)]
Leads [GroupID(PK), LeaderID(FK)]

But this actually allows many leaders to lead a group.

Thank you. Any input is appreciated.

Solution

  • Well, you could just have leaderOf as a (unique) field in the Leader table. If it's NULL, the leader doesn't lead any group, and if it's a number then that number is the ID of the group being led.

    If you have a users or players table somewhere, it might even be better to put that leaderOf field in that table and drop the Leader and Leads tables altogether.