Define relationship between tables 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.

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:

Supplier Table

supplier_ID	supplier_Name	supplier_Address
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

Product Table

ID	supplier_ID	Product	Quantity	PurchasedAmt
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

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?