In a many-to-many relationship, one or more rows in a table can be related to 0, 1 or many rows in another table. In a many-to-many relationship between Table A and Table B, each row in Table A is linked to 0, 1 or many rows in Table B and vice versa. A 3rd table called a mapping table is required in order to implement such a relationship.
To illustrate the many-to-many relationship consider the sample table design for a bank below:
customers table
| column | |
| cust_id |
primary key |
| lastname |
|
| firstname |
|
products table
| column | |
| product_id |
primary key |
| name |
|
mapping table
| column |
| cust_id |
| product_id |
Assume that the bank has only 2 customers and 2 products:
| cust_id | lastname | firstname |
| 0001 |
henry |
john |
| 0002 |
smith |
adam |
| product_id | name |
| 0001 |
savings |
| 0002 |
credit card |
| cust_id | product_id |
| 0001 |
0001 |
| 0001 |
0002 |
| 0002 |
0002 |
Notice from the mapping table, John Henry has 2 facilities with the bank - a Savings account and a Credit Card. Also, notice that both the customers own Credit Cards issued by the bank. This means that with the way the database tables are designed:
1. One customer can have 0, 1 or many products
2. One product can be owned by 0, 1 or many customers
See also:
One-to-One relationships and
One-to-Many relationships
