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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice