What is many to many relationship in database?

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table.

For example:

a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers.

Relational database systems usually don't allow you to implement a direct many-to-many relationship between two tables. Consider the example of keeping track of invoices.

If there were many invoices with the same invoice number and one of your customers inquired about that invoice number, you wouldn’t know which number they were referring to. This is one reason for assigning a unique value to each invoice.

Example of many to many relationship:

The following example includes a Students table, which contains a record for each student, and a Classes table, which contains a record for each class. A join table, Enrollments, creates two one-to-many relationships—one between each of the two tables.

The primary key Student ID uniquely identifies each student in the Students table. The primary key Class ID uniquely identifies each class in the Classes table. The Enrollments table contains the foreign keys Student ID and Class ID.