Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 15

Understanding Database Relationships

Recommended resources

Hello! In our last lesson, we developed a method for identifying relationships between entities by analyzing business descriptions. We learned to think of entities as the "nouns" and relationships as the "verbs" that connect them.

Now that we know how to find these connections, our next step is to classify them. This lesson focuses on the cardinality of a relationship, which defines the numerical rules governing it. By the end of our session, you'll be able to distinguish between the three fundamental types of relationships: one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N). This skill is essential for accurately modeling business rules and drawing correct Entity-Relationship Diagrams (ERDs).

1. What is Relationship Cardinality?

In the previous lesson, we identified a relationship like "a CUSTOMER places an ORDER". But this description is incomplete. To build a functional database, we need to ask more specific questions:

  • How many orders can a single customer place? One? Many?
  • How many customers can be associated with a single order? Just one? Or could an order be shared by multiple customers?

These questions are about the cardinality of the relationship. Cardinality defines the minimum and maximum number of instances of one entity that can be related to instances of another entity. Getting this right is crucial, as a mistake can lead to a database that doesn't correctly reflect the real-world rules it's supposed to model.

The following video introduces why thinking about "how many" is a vital part of the database design process.

Entity Relationship Diagrams

This video from the Decomplexify channel explains the importance of defining the cardinality of relationships.

Watch the segment from the start of the discussion on relationships up to the end of the cardinality decisions. Focus on the questions the presenter asks about the "pet ownership" scenario. This illustrates the thought process required to define business rules precisely.

2. The Three Fundamental Relationship Types

When we focus on the maximum number of associations between entities, three primary types of relationships emerge. The image below provides a quick visual summary using simple examples.

Types of Relationships in Database Design
This diagram shows the three main relationship types: one-to-one (Husband-Wife), one-to-many (Football team-Footballer), and many-to-many (Student-Subject).

To understand these properly, we determine the cardinality by reading the relationship in both directions. Let's examine each type in detail.

Database table relationships

This article from Metabase Learn provides clear definitions and examples for each relationship type.

Read the sections titled One-to-one relationship, One-to-many relationship, and Many-to-many relationship. As you read, focus on: The definition of each relationship type. The example provided (e.g., Employee-Computer, Customer-Order). How the relationship is determined by asking "how many?" from both directions.

Let's summarize what you just read:

a) One-to-One (1:1) Relationship

  • Definition: A single record in Table A is related to at most one record in Table B, and a single record in Table B is related to at most one record in Table A.
  • Example: An EMPLOYEE is assigned exactly one company COMPUTER, and a company COMPUTER is assigned to exactly one EMPLOYEE.
  • How to read it:
    • From EMPLOYEE to COMPUTER: One employee is assigned one computer.
    • From COMPUTER to EMPLOYEE: One computer is assigned to one employee.
  • Note: This is the least common relationship type. Often, the information could be combined into a single table unless there's a specific reason to separate it (like security or organization).

b) One-to-Many (1:M) Relationship

  • Definition: A single record in Table A can be related to many records in Table B, but a single record in Table B can only be related to one record in Table A.
  • Example: A CUSTOMER can place many ORDERs, but each ORDER belongs to only one CUSTOMER.
  • How to read it:
    • From CUSTOMER to ORDER: One customer can have many orders.
    • From ORDER to CUSTOMER: One order belongs to one customer.
  • Note: This is the most common and fundamental relationship type in relational databases.

c) Many-to-Many (M:N) Relationship

  • Definition: A single record in Table A can be related to many records in Table B, and a single record in Table B can also be related to many records in Table A.
  • Example: A STUDENT can enroll in many COURSEs, and a COURSE can have many STUDENTs enrolled.
  • How to read it:
    • From STUDENT to COURSE: One student can enroll in many courses.
    • From COURSE to STUDENT: One course can have many students.
  • Important Preview: As the article mentioned, relational databases cannot directly implement M:N relationships. They must be broken down into two 1:M relationships using a third table, often called a join table or composite entity. We will cover exactly how to do this in Module 6. For now, the key is to be able to identify an M:N relationship from the business rules.

The next video provides a concise summary of these three types using the standard "Crow's Foot" notation you'll be using to draw ERDs.

Entity Relationship Diagrams

Let's return to the "Entity Relationship Diagrams" video to see a clear visual summary.

Watch this short segment that explains the three basic kinds of relationships. Pay attention to how the "crow's foot" symbol represents the "many" side of a relationship.

3. More Examples in Action

Let's solidify your understanding with a few more practical examples. The following video walks through several scenarios, explicitly stating the business rules and then identifying the relationship type.

ERD: Relationship Cardinalities

This video from Dustin Ormond provides quick, clear examples of binary relationships.

Watch the following three short segments: Binary one-to-one: The Employee and Parking Space example. Binary one-to-many: The Product Line and Product example. Binary many-to-many: The Student and Course example. Notice how in each case, the relationship is read from both directions to determine the cardinality. You'll also see symbols for "optional" (a circle) vs. "mandatory" (a line). We will explore that concept in detail in our very next lesson!

Conclusion

In this lesson, we moved from simply identifying relationships to classifying them based on their cardinality. Understanding the difference between one-to-one, one-to-many, and many-to-many relationships is a non-negotiable skill for correct database design. It allows you to translate complex business rules into a structured, logical model.

Key Takeaways:

  • Cardinality defines the numerical rules of a relationship, answering "how many?".
  • There are three fundamental relationship types determined by the maximum cardinality:
    • One-to-One (1:1): One instance is related to at most one other instance (e.g., Manager manages one Department).
    • One-to-Many (1:M): One instance is related to many other instances (e.g., Department has many Employees). This is the most common type.
    • Many-to-Many (M:N): An instance on either side can be related to many instances on the other side (e.g., Student enrolls in many Courses).
  • To determine the relationship type, you must analyze the business rules and read the relationship from both directions.

So far, we've focused on the maximum number of connections. In our next lesson, we will add another layer of precision by defining the minimum number. This is known as participation, and it determines whether a relationship is mandatory or optional.