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

Advertising

Advertising:
Back
Top