2

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

Multiple small pivot tables between each tables

Create one big pivot table between all table

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?

Community
  • 1
  • 1
totymedli
  • 29,531
  • 22
  • 131
  • 165
  • Does the user belong to a region independent of a team? Or does a user belong to a specific region because that person's team is in that specific region? – AgRizzo Dec 19 '13 at 19:14
  • @AgRizzo No, regions and teams ar two separate things. A user can be part of a region but also part of a team that is in a totally different region. For example user lives in Hungary but works in an american team remotely. – totymedli Dec 19 '13 at 19:22
  • Go with the first solution - the second is a relationship between the user, Hungary, and American team. Then you would need to create another entry for every variation of team and region for that user. – AgRizzo Dec 19 '13 at 19:23
  • @AgRizzo Yes, but if I need to add a fourth table, there will be a lot of pivot tables. – totymedli Dec 19 '13 at 19:30
  • A lot of tables is not necessarily a bad thing, it is expected when you are storing normalised data. As an aside, the tables you are describing are [junction tables](http://en.wikipedia.org/wiki/Junction_table), not [pivot tables](http://en.wikipedia.org/wiki/Pivot_table). You should have as many junction tables as required, by the sounds of it you have three seperate relationships, Teams >> Users, Users >> Regions, Regions >> Teams. All three are independant so each relationship should have its own junction table, so use your first method. – GarethD Dec 19 '13 at 19:44
  • The junction tables represent the relationship, which you have not described as being 3 way, but a standard M:N. Conceptually, think about how much redundant data you would have in your 2nd example. Also, here is a relevant article on the [4th Normal Form](http://en.wikipedia.org/wiki/Fourth_normal_form) – AgRizzo Dec 19 '13 at 20:08

1 Answers1

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>
marekful
  • 14,986
  • 6
  • 37
  • 59