Relationship is the most crucial concept in relational database management system (RDBMS). The whole existence of RDBMS is due to the Normalization process and normalization is founded on defining and maintaining relationship between different entities.
First of all, let's think about normalization in brief.
Speaking very bluntly, normalization is the process of breaking table into smaller ones so as to:
- Eliminate data redundancy
- Enforce data integrity
A normalized database is free from unnecessary copies of same data which helps to improve the data consistency and integrity.
Read first: What is Data Redundancy in Database?
Let's start the discussion with the same table (as we saw in above link) to understand how breaking tables helps to reduce redundancy and improve data consistency. Here is the inventory record of a store.
|1001||Mittal Products||308 Milan Street, Corner Stone Alley, Pokhara, Kaski District, Gandaki Zone||AR Drone 305||35||45000|
|1002||Kaveri Traders||Jeevan Marga – 01, Kalanki, Kathmandu||PK Kaplan 20||200||8000|
|1003||Mittal Products||308 Milan Street, Corner Stone Alley, Pokhara, Kaski District, Gandaki Zone||PK Kaplan 20||200||7500|
|1004||Nanda Pvt. Ltd.||Sundar Galli, Kuleshwor, Kathmandu||AR Drone 305||25||3800|
|1005||Mittal Products||308 Milan Street, Corner Stone Alley, Pokhara, Kaski District, Gandaki Zone||Nanda DK S28||10||2000|
In above table you are can reduce data repetition if you break this table into two - Stock and Supplier. Following is the table description:
Supplier: supplier_ID, supplier_Name, supplier_Address
Stock: stock_ID, supplier_ID, Product, Quantity, PurchasedAmt
(Underlined fields are primary keys)
Breaking inventory_record table into two tables (supplier & stock) will produce the following result:
|S001||Mittal Products||308 Milan Street, Corner Stone Alley, Pokhara, Kaski District, Gandaki Zone|
|S002||Kaveri Traders||Jeevan Marga – 01, Kalanki, Kathmandu|
|S003||Nanda Pvt. Ltd.||Sundar Galli, Kuleshwor, Kathmandu|
|1001||S001||AR Drone 305||35||45000|
|1002||S002||PK Kaplan 20||200||8000|
|1003||S001||PK Kaplan 20||200||7500|
|1004||S003||AR Drone 305||25||3800|
|1005||S001||Nanda DK S28||10||2000|
You saw how only one entry of that long supplier name and supplier address is sufficient to keep record of every purchase of inventory.
Now because the data about a transaction is distributed between two tables, there should be some mechanism to link these two and retrieve the required supplier when its ID is supplied. Such mechanism in RDBMS is the relationship. To make these two tables work for our future searches and retrieval, we need to create relationship between Supplier table and Produtct table.
What is the relationship between tables in relational databases?
A relationship, in databases, is a situation that exists between two tables that define how records in one table is related to the records in other tables. Practically, we may need to reflect three different kind of relationship, namely:
- One to one relationship
- One to many relationship
- Many to many relationship
One to one relationship
A one-to-one relationship exists between two table if one record in a table can be related to only one or zero records in another table. An example can be a relationship between Employee table and Employee_Fund table if they have fields like below:
Employee: emp_ID, emp_Name, emp_Address, emp_DOB, emp_DOJ
Employee_Fund: epf_ID, epf_Office, epf_totalAmount
It is assumed that emp_ID and epf_ID contain same data. The relationship created between these tables is through emp_ID and epf_ID field which is primary key field in their respective tables.
One to many relationships
One-to-many relationship between two tables is most common type of relationship which exists when one record in first table is related to many or zero records in second table. The relationship between the Supplier and Product table (in our example above) is of one-to-many type because one record in supplier table can be related to many records in Product table. Talking it in more general, we can say one supplier may supply many products and thus one-to-many relationship.
One to many relationship is always created when you link the primary key of a table with non-primary key of another table. Often the relation between a primary key and foreign key results into one-to-many relationship.
Many to many relationship
This type of relationship is very common in real world situation such as one doctor may check many patients and one patient may be attended by many doctors. Similarly, one teacher many students at the same time one student may be taught by many teachers.
Though this is found in many real world situations, creating many-to-many relationship between two database tables is not possible. To reflect many-to-many situation in databases, we create a third table called junction table.
(many) firstTable -> (one) junctionTable -> (many) secondTable
Related: Relationship Types in MS Access Database
Related: Which is not a valid relationship in MS Access?