1. Introduction to Table Relations

Table Relations are highly needed for the modern database systems like Oracle, SQL Server etc., The link between the tables are met by Table Linking. We do this by joining a column in one table with a column in another table. When we do so, there are four kinds of relations which will arise. The table relations are below:

1. One-to-One Relations
2. Many-to-One Relations
3. One-to-Many Relations
4. Many-to-Many Relations

We will explore each relations in this article.

2. One to Many & Many to One Relations

In One-To-Many relations, a single column value in one table will have one or more dependent column value(s) in another table. Now, we will look at the below Picture:

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 column. Here, the CustID column in the Order Table is showing which customer placed the order. Note, 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, but there is only one corresponding CustID X in the Customer Table.

So if we look at the table relations left to right (Customer to Order), it is a One-To-Many Relations. If you look at the relations right to left (i.e.) Order to Customer, then it is a Many-To-One Relation. In other words, Customer to Order is a One-To-Many Relation and Order to Customer is a Many-To-One Relation.

Modelling The Relation

In a modern database system, we can model this with a Foreign Key and Unique or Primary Key. In our Case, CustID in the Customer table is Primary or Unique Key. At the same time, the CustID in the Order table is a Foreign Key which refers the column CustID in the Customer table. Note, if we designed the One-to-Many Relation between two tables, then Many-To-One relation results automatically.

3. Many-To-Many Relation on Transaction Table

Hope we know what is Many-to-Many as we are done with past part and have some insight on Table Relations. So, the quiz is how do we model it in the database system. For a pure Many-To-Many Relation, a third SQL Table is needed. This third Table acts as a bridge between the joining tables to form a “Many-to-Many” Relation. Also, the Bridge Table stores common data between the Many to Many Relation Tables. Now we will look at the below picture:

In the above case, Transaction SQL table acts as a bridge table. It has data common to Product and Customer. The Transaction table has two One-to-Many Relations. One is between Transaction and Customer. The other one is between Product and Transaction. If we pick a Customer Id row in the Customer Table, there are many customer id rows in the Transaction Table. Similarly, One product id in the Product table has many mapping items in the Transaction Table. So, the whole result gives answers to:

1. A customer who purchased multiple products.
2. A product, which is purchased by Multiple customers.

How is it Many-to-Many Table Relation?

So how do Many-to-Many table relation exists between Product and Transaction tables?

1. First, let us look at the Linkage shown in the picture from right to left. A single customer ID 126 is mapped to multiple customer Ids (126) in the Transaction Table. All the Transaction Table’s repeated customer Ids (126) may 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 identified by 126 purchased Many Products”
2. Now we can analyze our table relation from Left to Right. A single Product A12 in the Product Table has repeated entries in the Transaction Table. All these same products (A12) has different Customer IDs C1, C2, C3… Cn. There is a corresponding unique row for each Customer like C1, C2 etc., in the Customer Table. The whole result is “A single Product A12 is purchased by Many Customer”

The table relation between Product and Customer is Many-to-Many via the bridge table Transaction. A customer bought many products and many customers can shop these products. So the relation between Product and Customer is Many-to-Many.

Modelling the Relation

1. ProdId and CustId in the Transaction Table forms a Primary key along with Transaction id which not shown here. The Prodid field points a single row in the Products Table. Likewise, the CustId field points to a row the Customer Table. Prodid in the transaction table refers the Product id in the Product Table using a foreign key. CusId in the transaction table refers the customer id in the Customers Table using another foreign key. So three fields ProdId,  CustId and Transaction id as a group acts as a Primary key for Transaction Table.
2. ProdId in the SQL Product table is a Primary Key.
3. CustId in the SQL Customer table is a Primary Key.

4. One-to-One Relation

One-to-One table relation can go on between two tables. Consider the below Picture:

The member id in the CAshCornerMembers SQL table is a Primary Key. Two tables VIPMember and MVPMember , refers this Primary Key field. Both MemberId fields in the tables VIPMember and MVPMember are Primary Keys and they point to the MemberId field of the third table through the Foreign Key relation. So, in this example, there are 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 in which they live. Also, note that a member can be a VIP member and an MVP member. In that case, both the table has his or her MemberId and refers a single row in the table CAshCornerMembers. Thus, we end up with two One-to-One relation.

• Java Reflection: Finding Instance Type
• Learn How to Use Order By Clause and Group By Clause

Categories: SQL Server

Tags: Many to Many, One to Many, One to One

This site uses Akismet to reduce spam. Learn how your comment data is processed.