Cardinality and Relationships

Map one-to-one, one-to-many, and many-to-many relationships between distinct data entities.

In this lesson, you will learn how to define the "rules of connection" between different entities in your database using cardinality.

What is Cardinality?

Cardinality defines how many instances of one entity can be associated with instances of another entity. Think of it as a set of rules that dictate how your data objects "talk" to each other.

The Three Types of Relationships

One-to-One (1:1): Each record in Entity A relates to exactly one record in Entity B, and vice versa. For example, a Citizen has one SocialSecurityNumber, and a SocialSecurityNumber belongs to only one Citizen.
One-to-Many (1:M): One record in Entity A can relate to many records in Entity B, but each record in Entity B relates to only one in Entity A. This is the most common relationship. Think of a Department and its Employees. One department can have many employees, but an employee typically belongs to only one department.
Many-to-Many (M:N): Multiple records in Entity A can relate to multiple records in Entity B. For example, Students and Classes. One student can enroll in many classes, and one class can contain many students.

Mapping the Relationships

To implement these, we use our knowledge of Primary Keys. In a One-to-Many relationship, we place the Primary Key of the "One" side into the "Many" side as a reference (often called a Foreign Key).

-- One Department (ID: 10)
-- Many Employees linked to Dept 10
INSERT INTO Employees (EmployeeID, Name, DeptID) VALUES (1, 'Alice', 10);
INSERT INTO Employees (EmployeeID, Name, DeptID) VALUES (2, 'Bob', 10);

For Many-to-Many relationships, we create a "junction table" to link them. If we have Students and Classes, we create a table called Enrollments:

-- Junction table linking Student 101 to Class 505
INSERT INTO Enrollments (StudentID, ClassID) VALUES (101, 505);

By mapping these relationships, we ensure data integrity—making sure that our records reflect the real-world business logic we defined in our entity analysis.

Key Takeaway: Cardinality dictates the structure of your database by defining whether entities connect as one-to-one, one-to-many, or many-to-many.