Home Contact Us About Us Useful Links

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) lastname firstname products table ============= product_id (primary key) name mapping table ============= 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         Database Tables Data Types Table Relationships Table Keys SQL - Lingua Franca Features Referential Integrity Normalization

This is DatabasePrimer.com   Informative. Illustrated. Original. © 2005 DatabasePrimer.com. All Rights Reserved.