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

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