Many to Many Relationships in Relational Databases (2024)

Relational databases enable users to store and retrieve data from related tables(1). They compose of tables, rows, columns, keys, atomic data, and relationships between tables. There are three types of relationships in these databases;

· One to One (1:1)

Example: In nature, each element has one formula and each formula represents one element.

· One to Many (1:M)

Example: In business, each staff works in one department and in each department, many staff can be working.

· Many to Many (M:N)

Example: In university, each student can attend many courses and in each course, there can be many students.

Many to Many(M:N) Relationship

Many to many relationships create uncertainty and duplications on data that will eventually result in wrong statements for queries(2). In the below example;

Each person can use many banks and each bank can have many customers.

In the Entity-Relationship (ER) Diagram, ‘person_id’ is specified as Primary Key, and Primary Keys should be unique. There are rows that have the same Primary Key in people table. SQL Server will be raising an error if this table is tried to create(3).

Violation of PRIMARY KEY constraint ‘PK__customer__543848DFE23A1B1A’. Cannot insert duplicate key in object ‘dbo.customer’. The duplicate key value is (1).

A common way to avoid problems while setting many to many relationships is creating a new table which is called ‘join table’, ’junction table’, ’cross-reference table’, ‘bridging table’, ‘intersection table’ in various resources.

Join table has to be composed of ‘person_id’, ‘bank_id’ from related tables’ Primary Keys and it is possible to add a new column in Join table (i.e. ‘account_type’). ‘person_id’ and ‘bank_id’ are both Primary and Foreign Keys in Join table. Primary Keys can’t be NULL values. Because of that every ‘person_id’ and ‘bank_id’ values should be filled from people and banks tables.

People and banks tables have ‘many optional’ to ‘many optional’ relationship. It means that a customer can be working with many banks or he/she can’t be working with any bank. Also, a bank can have many customers or it can’t be having any customers.

In people_banks table, each ‘person_id’ should match up with only one ‘person_id’ in people table. So that, each row in people_banks table has ‘one mandatory’ relationship with people table. This rule is also valid for the relationship between banks and people_banks tables.

References

1. https://www.oracle.com/database/what-is-a-relational-database/

2. https://documentation.sisense.com/8-1/managing-data/working-with-data/many-to-many.htm#gsc.tab=0

3.https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.ddi.doc/ids_ddi_186.htm