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_idlastnamefirstname
0001 henry john
0002 smith adam
product_idname
0001 savings
0002 credit card
cust_idproduct_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

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