You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Many-to-many relationship
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
products table
mapping table
Assume that the bank has only 2 customers and 2 products:
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
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
Advertising
Advertising: