November 21, 2010

SQL 2005 - Table relationships - One-To-One, One-To-Many, Many-To-Many


The relationship between the tables are very important for the modern database systems like Oracle, SQL Server etc., The relationship between tables are performed by linking a column in one table with a column in another table. When we do so there are four different type of relationship will arise. The relationships are listed below:

1) One to One Relationship2) Many to One relationship3) One to Many relationship4) Many to Many relationship

We will explore each relationship here.

One to Many & Many to One Relationship

In One-to-many relationships, a single column value in one table will have one or more dependent column value in another table. Look at the below Picture:
Fig 1. One to Many

Here, there are two tables. One is Customer table, which has all the customers. The other table is Order table, which has all the orders uniquely identified by the OrderID. The CustID column in the Order table is showing which customer placed the order. In this case, there will be multiple values for the same CustID in the Order table. If customer X has placed 4 Orders, then there are 4 CustID (X) in the Order table, and there is only one corresponding CustID X in the customer table.

So if you look at the relationship from left to right (Customer to Order), it is a One-To-Many relationship. If you look at the relationship from right to left  (i.e.) Order to Customer, then it is a Many-to-One relationship. Simply, Customer->Order is a One-to-Many relationship & Order->Customer is a Many-to-One relationship.

Modelling the relation

In a relational database system, you can model this with a foreign key and unique Key. In our Example, CustID in the Customer table is Primary or Unique Key. CustID in the Order table is a foreign key that refers the column CustID in the Customer table. Note that if you modelled the One-to-Many relationship between two tables, then many-to-one relationship automatically exists. The professional way of symbolic representation is placing a ¥ on the left side point and 1 in the right side point (In our diagram).  

Many to Many Relationship

Hope you have an idea of what I am going to say here as you already read the previous section. The question is how do we model it in the relational database system. For a perfect Many-to-Many relationship, a third table is required. The third table acts as a bridge between the two connecting tables that want to establish a Many-to-Many relationship. The bridge table stores the common information between the Many to Many relationship tables. Have a look at the below picture:

Fig 2. Many-to-Many
In the above example, Transaction table acts as a bridge table. It has information common to both product and customer. The Transaction table has two One-to-Many relationships. One is between Transaction & Customer and the other one is between Product & Transaction. For a given Customer Id row in the Customer table, there exist multiple customer id rows in the transaction table. Similarly, One product id in the Product table has multiple entries in the transaction table. Together the result gives answers to:
1) A customer who purchased multiple products. 
2) A product, which is purchased by Multiple customers.

So how do “Many-to-Many” relationship exists between Product and Transaction tables?

1) First, let us look at the Linkage shown in the picture from left to right. A single customer id XYZ is mapped to multiple repeated customer ids (XYZ) in the Transaction table. All this transaction table’s repeated XYZ customer ids have different Product Ids P1, P2,P3...Pn. All these products in the transaction table will have a unique row in the Product table. So the whole result is “A single customer XYZ purchased Many Products

2) Let us have a look at the diagram from Right to Left. A single product ABC in the product table has repeated entries in the Transaction table. All these same products (ABC) has different customer ids C1,C2,C3…Cn. These a corresponding unique row for each Customer like c1,c2 etc., in the Customer Table. The whole result is “A single product ABC is purchased by Many Customer

Hence, the relationship between Product and Customer is Many -to-Many via the intermediate bridge Transaction. Simple, there are many products purchased by many customers and they together forms the concept of Products Transaction by customers.

Modelling the Relation

1) ProdId and CustId in the transaction table together forms a Primary key. That is; both the Prodid and CustId column uniquely identify a row in product and customer tables respectively. Prodid refers the Product id in the Product table using a foreign key. CusId refers the customer id in the Customers table using another foreign key. So ProdId and CustId together forms a Primary key in Transaction table and as an individual, they act as a foreign key for a table to the left or right.
2) ProdId in the Product table is a Primary Key.
3) CustId in the Customer table is a Primary Key.

One to One Relationship

One to one relationship can exist between two tables. I hope you already guessed it. You are right if your guess is “The second table column that refers the first table column is a primary key as well as foreign key”. Consider the below-specified Picture:

One to One
Fig 3. One-to-One

It looks like an inheritance for Front-end developers. OK. That holds true for our example also. The member id in the CAshCornerMembers table is a Primary key. Two tables VIPMember, as well as MVPMember, refers this primary key column in the table CAshCornerMembers. Both MemberId fields in the tables VIPMember and MVPMember are primary keys and at the same time, they point to the MemberId field of the other table CAshCornerMembers using the Foreign key. So in this example, there exist two One-to-One relations.

How is it One-To-One?

The answer is simple! The columns involved in the Mappings are Unique in the table they present. Also, note that a member can be a VIP member as well as MVP like our Mahesh Chand. In that case, both the table has his MemberId (Once) and refers the Single entry in the table CAshCornerMembers.

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Friend :)