What are the three types of relationships that can exist between entities?

Data entities in a database are related in one of three ways: one-to-one (1-1), one-to-many (1-M), and many-to-many (M-M).

In the following one-to-one example, for every EMPLOYEE entity occurrence in the database, there can exist only one corresponding PROJECT entity occurrence.

In the following one-to-many example, for every DEPARTMENT entity occurrence in the database, there may exist one or more corresponding EMPLOYEE entity occurrences.

In the following many-to-many example, for every SKILL entity occurrence in the database, there can exist one or more corresponding EMPLOYEE entity occurrences. For every EMPLOYEE entity occurrence in the database, there can also exist one or more corresponding SKILL entity occurrences.

Other types of data relationships

In addition to relationships between two entity types, the following types of data relationships are acceptable in an entity-relationship model:

A relationship can be defined for only one entity type

. For example, to define a relationship between different employees in an organization, you might combine different data occurrences from the EMPLOYEE entity. For example, you might express the relationships among employees as MANAGES and REPORTS TO. See the following entity-relationship diagram of Commonweather Corporation for an example.

This is not a common relationship type, as the data stored in table B could just have easily been stored in table A. However, there are some valid reasons for using this relationship type. A one-to-one relationship  can be used for security purposes, to divide a large table, and various other specific purposes.

In the above example, we could just as easily have put an HourlyRate field straight into the Employee table and not bothered with the Pay table. However, hourly rate could be sensitive data that only certain database users should see. So, by putting the hourly rate into a separate table, we can provide extra security around the Pay table so that only certain users can access the data in that table.

One-to-Many (or Many-to-One)

This is the most common relationship type. In this type of relationship, a row in table A can have many matching rows in table B, but a row in table B can have only one matching row in table A.

One-to-Many relationships can also be viewed as Many-to-One relationships, depending on which way you look at it.

In the above example, the Customer table is the “many” and the City table is the “one”. Each customer can only be assigned one city,. One city can be assigned to many customers.

Many-to-Many

In a many-to-many relationship, a row in table A can have many matching rows in table B, and vice versa.

A many-to-many relationship could be thought of as two one-to-many relationships, linked by an intermediary table.

The intermediary table is typically referred to as a “junction table” (also as a “cross-reference table”). This table is used to link the other two tables together. It does this by having two fields that reference the primary key of each of the other two tables.

The following is an example of a many-to-many relationship:

So in order to create a many-to-many relationship between the Customers table and the Products table, we created a new table called Orders.

In the Orders table, we have a field called CustomerId and another called ProductId. The values that these fields contain should correspond with a value in the corresponding field in the referenced table. So any given value in Orders.CustomerId should also exist in the Customer.CustomerId field. If this wasn’t the case then we could have orders for customers that don’t actually exist. We could also have orders for products that don’t exist. Not good referential integrity.

Most database systems allow you to specify whether the database should enforce referential integrity. So, when a user (or a process) attempts to insert a foreign key value that  doesn’t exist in the primary key field, an error will occur.

In our example, Orders.CustomerId field is a foreign key to the Customers.CustomerId (which is the primary key of that table). And the Orders.ProductId field is a foreign key to the Products.ProductId field (which is the primary key of that table).

How many types of relationships that exist between entities?

Can a relation have 3 entities?