Relationship Types in MS Access Database

The Role of Primary Key in Relationship

Primary key in a table makes sure that the values there for every record is unique. That means it is not repeated in other rows. Similarly the primary key is never empty.

When two tables are connected with each other with a relationship, there can be one of the following types of relationship:

* one-to-one

* one-to-many

* many-to-many

Many-to-many relationship is not possible to create in database software. So we create a junction table to reflect many to many. Lets take an example of Products and Suppliers table. There can be many products from one Suppliers. And many suppliers for any one Product. Colgate produces toothpaste, tooth brush and many other. And, tooth paste is produced by Colgate, Pepsodent and many others.

So, in real world many-to-many relationship exists between different entities. But in case of database software, it can’t understand and enforce many-to-many. So we create a junction table like Purchase which has one-to-many relationship with Suppliers and one-to-many with Products. This way many-to-many between suppliers and products is reflected.

One-To-One Relationship is in fact not needed in most practical situations. If there are two tables joined with one-to-one, it is very possible that you combine those two tables and create one. In rare situations, organizations would like to have two different tables even when they are connected with one-to-one. Take an example of Employee table and Provident Fund table. Every employee is unique and every Provident Fund is unique. You could just bring the fields of Provident Fund in Employee table and make it one. But if there is any reason for the organization to have Provident Fund as a separate table, we’ll create one-to-one relationship between them.

you can enforce one-to-one relationship by connecting primary key of first table with the primary key of second field.

One-To-Many Relationship is the most common type of relationship. Most of the connections between tables are made one-to-many. When you drag a foreign key of a table and drop into primary key of another table, the relationship created is, very obviously, one-to-many. It is because primary key is unique and foreign key is not unique.

The meaning of 1 and infinity symbols in relationship diagram is to indicate which table has one and which has many records in one-to-many relationship. If you create one-to-one relationship between two tables, you’ll see 1 in both end of relationship line.

That’s all for the Sushil’s query. However, recommend you to watch following 7 minutes video to understand why relationship databases and the types of relationships as well as how to create relationships in MS Access. I’ve picked up this video after looking over many videos that exactly matches what Sushil wants to learn. If you want more information regarding relationship in RDBMS (Relational Database Management System)