Many to Many
Diagram M2M.png
How to read For any A there are possibly many Bs. For any B there are possibly many As.
Relevant relations Either side of this relationship can be optional or mandatory.

Consider the alternatives of embedding a column from one table in the other table: embedding b_id in the A table or a_id in the B table. Suppose we put the b_ids in A. By embedding the b_id in the A table, we are associating one and only one B entity with a particular A entity. This cannot be the situation according to the ER diagram, because for any A entity there are possibly many associated B entities. By rule #1 we can't do this.

Suppose we put the a_ids in B. By embedding the a_id in the B table, we are associating one and only one A entity with a particular B entity. This cannot be the situation according to the ER diagram, because for any B entity there are possibly many A entities—-so you would possibly have to put many a_ids in one record. By rule #1 we can't do this.

The only other alternative is to create a third table: the R relation with a_id and b_id embedded in it. First we need to determine what the key should be. The obvious choies are to use either a_id alone or b_id alone. Consider a_id: Again, for every A entity there are potentially many B entities. This means that a_id might appear in many rows of R. This means that it cannot be the key by itself. The situation is the same for b_id. The other alternative is to use the combination of a_id and b_id as the primary key. Each row in R represents a relationship between an A entity and a B entity. Since there is no reason to list a particular relationship twice, the combination of these two columns can serve as the primary key.

Given the above discussion, the table structure is as follows:

A(__a_id__, a_name, <a_other>)
B(__b_id__, b_name, <b_other>)
R(__fk_r_a*, fk_r_b*__, <r_other>)

Given this table structure, we must only determine which foreign keys can be null and which are unique. There are two foreign keys in these tables. Every row in the R table represents a relationship between an entity in the A table and an entity in the B table. If either column is null, it should not be a row in the R table since this would mean that there is not a relationship between the two entities. The column fk_r_b cannot be unique since each B entity possibly can be in many relationships. The column fk_r_a cannot be unique since each A entity possibly can be in many relationships. Given this information, we have the following SQL create statements:

create table A
  (a_id type,
   a_name type,
   <a_other>,
   primary key (a_id));
create table B
  (b_id type,
   b_name type,
   <b_other>,
   primary key (b_id));
create table R
  (fk_r_a type,
   fk_r_b type,
   <r_other>,
   primary key (fk_r_a, fk_r_b),
   foreign key (fk_r_a) references A,
   foreign key (fk_r_b) references B);

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License