What are entities relationships in data modeling?

Working with data efficiently means designing a database. The first step in this process is data modeling, that is, conceptual design.

In this shot, we will look at entities relationships. Relationships are the glue that holds the entities (or tables) together. We use them to connect related information between entities.

We will cover:

• One-to-many relationships (1:M).
• Many-to-many relationships (M:N).
• One-to-one relationships (1:1).

Let’s get started.

One-to-many relationships (1:M)

Let’s start with the most common type of relationships we have in a relational model: one-to-many.

In this relationship, one row in an entity, also known as the parent table, which can be referenced by multiple records in another entity, the child table. A typical example of this is a parent who can have many children. Let’s see more examples.

Examples

• Customer-purchases: In a shop, one customer can have several purchases.
• Department-employees: In most companies, we find many people working in one given department.

Note: The one-to-many relationship is created using a primary key-foreign key relationship.

Let’s draw a visual representation of the one-to-many relationship.

Many-to-many relationships (M:N)

Another common type of relationship to learn is a many-to-many one. A M:N relationship is where more than one record in a table is referenced by multiple records in another table. This relationship is only used in logical models. In a physical database, it is implemented using a 1:M relationship, in addition to a junction or join table.

Examples

• Teachers-courses: A teacher teaches one or many courses, and one course can be taught by one or more teachers.
• Doctors-patients: A doctor can have one or more patients. This is also true for a patient, who can have one or more doctors.

Note: Notice that there’s no connection between these tables. When implementing this relationship, we need to add a third table that we call a join table. We can name it Course_teacher (course_id, teacher_id).

One-to-one relationships (1:1)

A 1:1 relationship is one where a record in one entity is associated with only one record in another entity.

Example

• Country-capital city: A country has one capital city.
• Spouse-spouse: In a company, and even in a monogamous marriage, a person is associated with exactly one spouse.

Summary

We’ve learned about the three main entities relationships that exist in data modeling. The discussion focused on:

• 1:M: A relationship where one record in an entity is related to one or more records in the other entity.
• M:N: This relationship happens when one or multiple rows in an entity can be associated with one or more records in another entity.
• 1:1: Here, one record in an entity is associated with exactly one record in another entity.