Database Table Relationship

This article talks about the Data Table relationship and explains the types with example. Also gives a guideline of how do you model each relationships.

Introduction

You may be disappointed to read this article if you do not know about database Constaints. But, just a basic knowledge is required. Just refer to C# corner for Constraints and then proceed.

 

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

1. One to One Relationship
2. Many to One relationship
3. Many to Many relationship
4. One to One relationship

We will explore each relationship here.

 

One to Many & Many to One Relationship

 

For a One to many relationship, a single column value in one table has one or more dependent column values in another table. Look at the following diagram:

 

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 is showing which customer placed the order. In this case, there will be multiple values for the same custid such as X 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, it is a Many to One relationship.

 

Modeling the relation

In the 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 foreign key that refers the column CustID in the Customer table. Note that if you modeled One to Many relationship between two tables then a 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.  I do it in the reverse way for easy understanding.

 

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 bridge between the 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 following diagram:

 

In the above example, the Transaction table acts as a bridge table. It has information common to both product and customer. I can say the transaction information is suitable for entering as columns to this table. The Transaction table has two One to Many relationships. One is between Transaction and Customer and other one is between Product and Transaction. One Customer Id in the Customer table maps to multiple customer ids (Of course same custid repeated) in the transaction table. Similarly, One product id in the Product table has multiple entries in the transaction table.

 

So how do "Many to Many" relationship exists between Product and Transaction table? 

1. First let us look at the Linkage shown in the diagram 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 id has different Product Ids p1, p2,p3...Pn. All these products 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 Right to left. A single product ABC in the product table has repeated entries in the Transaction table. All these repeated products (ABC) has different customer ids C1,C2,C3…Cn. These a corresponding unique row for each Customers 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.

 

Modeling 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 this table. Prodid refers the Product id in the Product table using a foreign key. CusId refers the customer id in the Customers table. So ProdId and CustId together forms a Primary key and as individual they are foreign keys.
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 following diagram:

 

 

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 exists two One to One relations.

 

How it is One-One?

 

The answer is simple! The columns involved in the Mappings are Unique in the table they present. Also not 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.

 

See you all in the next article.