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 |
column | |
---|---|
product_id | primary key |
name |
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 |
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