1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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
cust_id primary key
products table
product_id primary key
mapping table

Assume that the bank has only 2 customers and 2 products:
0001 henry john
0002 smith adam
0001 savings
0002 credit card
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

Share This Page