This topic deserves special attention because many people from database designers to application developers (sometimes one person does both) do not get it right. A Many-to-Many relationship occurs when each instance from the entity on the left can have many instances from the entity on the right, and vice versa.

Some examples of many-to-many relationships are as follows:

  1. Each order can contain many items. Each item can also belong to many orders as it can be purchased by different customers.
  2. Each student can be signed up for many coursers. Each course can be assigned to multiple students.

Incorrect implementation of many-to-many relationships

We see this problem when people are trying to use foreign keys to solve this problem. Let’s illustrate it with the second example listed above as follows:

Unresolved many-to-many

What we are trying to say here is that each student can take many courses and that each course can be taken by many students. So to implement this we have put a FK to the student table which will point to the course table.

Do you see any problems with this?

  • Yes!!! we will have repeated student rows for each different course a student is taking.
  • At the moment you do this your database would no longer be in 3NF because we would have repeated groups.
  • This issue above can potentially yield to update anomalies.
  • This relationship is said to be unresolved.

Correct implementation of many-to-many relationships

In order to correct the many-to-many implementation above we have to resolve relationship. This is done as follows:

  1. By adding another table between the two entities; this table is called associative entity or intersection table.
  2. By making the PK of the associative entity a composite key that consist of each of the parent’s PK (We are using Identifying relationship).
  3. By naming the intersection table as follows (not required by DBMS, but suggested): ENTITYA_ENTITYB. In our case STUDENT_COURSE or STUDENT_COURSE_REL.
  4. By Changing the cardinality of the parent tables to be One-to-Many to the associative entity.

Let’s see it in action:

Resolved many-to-many

What improvements do you see?

  • The student is defined once so we avoid duplicate data.
  • The courses are defined once and we avoid the same problem as if we would have put the FK in the course table in scenario #1.
  • We do not run the risk of update anomalies.
  • We can add meaningful fields in the associative entity. Those are called the Fixed Intersection data.

Alert: The FID will become your FACT tables and the parent entities would become your DIMENSION tables in OLAP database structures. Dimensional modeling is a different modeling technique  which uses a lot of denormalization so that you can yield better query performance as data is only 1 join away at most.