Why is a new table created in a many to many relationship?

Question

I am creating a database to store information about musical instruments in. I am using a EER diagram in MySQL workbench to visualise the relationships between each table.

When I create a many to many (n:m) relationship, a new table is created containing both primary keys of the tables involved in the new relationship. Other relationships, like a one to many (1:n), have just added the primary key of the second table to the original one as a foreign key and have not created an extra table.

For example, I am trying to create a relationship which shows that 1 instrument can feature on many CDs and 1 CD can feature many instruments.

Why is this extra table created and what is it used for?

Thanks

Answer 1

This table has many names: "mapping" table, "indirection" table, and others. It is the link between the relationship between CDs and instruments.

If an instrument has a column for CDs, an instrument can only reference a CD and vice versa.

If you have the same instrument with different CDs in multiple rows, the schema is denormalized. You would need an identical relationship in the CD rows and that is not guaranteed.

Instead you have this table that identifies a relationship between a CD and an instrument. The same instrument or CD can appear many times in this table allowing for a CD to belong to multiple instruments and vice versa.

With mapping table:

CDs    Mappings    Instruments
A       A <-> E        E
B       A <-> F        F
C       C <-> G        G
D       D <-> G

Without mapping table:

  CDs
CD   Instrument
A    E
A    F
B    null
C    G
D    G

---

For clarity, I'll explain why another table is not necessary in a one-to-many relationship. The table that can be related to only one entity in another table can store this relationship in a single row. Multiple rows of the identical item are unncessary. You could use a mapping table, but then the column for the "one" item would have to be unique, and this makes the additional table unnecessary since there can only be one row per item anyway.