Should I create one big pivot or multiple small pivots in a great many to many connection?

Question

I have the following tables: Users, Regions and Teams. Every tables are in many-to-many connection with eachother and I need every connection from every direction. In other words:

• Users can be in multiple Regions and Teams.
• Regions can have multiple Users and Teams.
• Teams can be in multiple Regions and have multiple Users.

How should I implement the pivot table between them?

Create multiple small pivot tables between each tables

Create one big pivot table between all table

Which one should I use? Is there actually a perfect solution or are there pros and contras? Would the answer different if I have to connect 4 or more tables the same way, where there would be 6 - or (n(n-1))/2 for any n - pivot table?

Answer 1

In certain cases depending on many factors in your business logic, use cases, defined by the exact roles of the entities, etc. you might find the second option useful, but I would definitely go for the first one. In professional database design, every many-to-many relation is normalised using a join table (not pivot). So you would have for e.g. User, Region and User_Region and so on for each many-to-many relation.

I personally find it very efficient to 1) have a composite primary key consisting of both columns in each join table and 2) let the primary key of the parent tables be a foreign key referencing the join table.

A simple example of the above for one relation on MySQL command line:

mysql> 
mysql> create table a (id int not null);
Query OK, 0 rows affected (0.01 sec)

mysql>  
mysql> alter table a add primary key (id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table b (id int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> alter table b add primary key (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table a_b (a_id int not null, b_id int not null);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> alter table a_b add primary key (a_id, b_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> alter table a_b add foreign key a_id_fk (a_id) references a (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> alter table a_b add foreign key b_id_fk (b_id) references b (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>