Multiple Junction Tables Between Same Entities

Question

I'm planning a database schema, and I've encountered a situation where I don't know the best way to proceed. I'm really looking for a list of pros and cons to each of my proposed solutions, perhaps followed by a recommendation for which would conform to best databasing practice.

So, the problem is that I have two entities with multiple many-to-many relationships between them. The two tables are Teams and People; a Team is composed of many People, and a Person could have one or many roles on the Team. Roles include team leader, team member, team follower, etc. A person may have more than one role for a particular team, but ideally a subset of these roles are mutually exclusive, while the rest are not.

Here are the solutions I've considered:

1) Create a separate junction table for each role. The presence of a row in each table signifies that a single person belongs to a single team, and the particular table indicates the person's role on the team. Mutually exclusive roles would have to be enforced at the application level.

2) Create a single junction table and store an enumeration on that table to specify which role a person has. A given person-team combo may have multiple rows in this table, one for each role that the person has with the team. Mutual exclusivity of certain roles would have to be enforced on the application level.

3) Create a single junction table, and store a list of boolean flags on the table, one for each role. Each person-team combo has a single row in the table, and the flags determine which roles the user has on that team. Mutual exclusivity could be enforced at the database level, because all mutually exclusive roles could share a single enumerated field on the table.

4) Create two junction tables. This is sort of a combination of (2) and (1) that allows mutual exclusivity to be enforced at the database level. There would be one junction table with an enumeration for mutually exclusive roles, and the other junction table (with enumeration) would handle all non-exclusive roles.

Is there anything I'm forgetting? Which option seems the most natural?

Thanks

score 1 · Answer 1 · answered May 09 '15 at 18:48

You've stumbled upon The Party Model! :)

Just store individuals and organizations in one table. This is called Single Table Inheritance. If you use a db like PG then the nulls don't take any space.

Same with Roles. Store them in one table unless it becomes unwieldy.

If you use an ORM like hibernate to update your db schema, it can switch to Class Table Inheritance very easily if you want. I'd say STI is easier to work with during development.

score 1 · Answer 2 · answered May 10 '15 at 05:30

Each table People and Team represent entities. The intersection or junction tables each represent a relationship between the entities. If you have multiple possible relationships, then it makes sense to have multiple junction tables.

You hint that some relationships could be mutually exclusive. That is a simple enough problem to fix.

create table CaptainOrMember(
    PersonID  int not null,
    TeamID    int not null,
    C_or_M    char( 1 ) not null,
    constraint PK_CaptainOrMember primary key( PersonID, TeamID ),
    constraint FK_CaptainOrMember_Person foreign key( PersonID )
        references People( ID ),
    constraint FK_CaptainOrMember_Team foreign key( TeamID )
        references Team( ID ),
    constraint CK_CaptainOrMember_OneOrOther check( C_or_M in( 'C', 'M' )
);

This defines both the Captain and Member relationships. Only one Person <-> Team entry can be made and it must be designated with either a 'C' or 'M'. So a person can be a Team Captain or a Team Member, but not both.

It has the added advantage of using one junction table for two relationships.

That is, unless you have some Captain-only and Member-only data you want to add to the relationship tuple. Then you can add two "sub-junction" tables. Here they are for illustration.

alter table CaptainOrMember add constraint UQ_CaptainOrMember_OneOrOther unique( PersonID, TeamID, C_or_M );

create table Captains(
    PersonID  int not null,
    TeamID    int not null,
    C_or_M    char( 1 ) not null,
    ...,
    ...,    <Captain related data>
    ...,
    constraint PK_Captains primary key( PersonID, TeamID ),
    constraint CK_Captains_OneOrOther check( C_or_M = 'C' ),
    constraint FK_Captains_Captain foreign key( PersonID, TeamID, C_or_M )
        references CaptainOrMember( PersonID, TeamID, C_or_M )
);

create table Members(
    PersonID  int not null,
    TeamID    int not null,
    C_or_M    char( 1 ) not null,
    ...,
    ...,    <Member related data>
    ...,
    constraint PK_Members primary key( PersonID, TeamID ),
    constraint CK_Members_OneOrOther check( C_or_M = 'M' ),
    constraint FK_Members_Member foreign key( PersonID, TeamID, C_or_M )
        references CaptainOrMember( PersonID, TeamID, C_or_M )
);

If an entry in table CaptainOrMember defines a particular person as the captain of a particular team, then the combination that that person and that team cannot be inserted into the Members table. It can only exist in the Captains table. And vice versa.

Multiple Junction Tables Between Same Entities

2 Answers2