Cardinality in ER Modeling

If 1:1 relationships are rare gems, one-to-many (1:N) relationships are the bedrock of database design. They appear everywhere: a department has many employees, a customer places many orders, a thread contains many messages, a folder holds many files. This cardinality ratio captures the fundamental hierarchical structure that pervades organizational, commercial, and computational domains.

One-to-many relationships encode a simple but powerful constraint: an entity on one side (the 'one' side, often called the parent) can associate with multiple entities on the other side (the 'many' side, often called the children), but each child entity associates with exactly one parent. This asymmetric constraint reflects real-world situations where one thing naturally 'owns', 'contains', or 'governs' many instances of another thing.

Understanding 1:N cardinality is essential because it:

• Forms the majority of relationships in most schemas
• Directly influences primary key / foreign key design
• Determines JOIN directions and query patterns
• Affects indexing strategies and query performance
• Shapes how we think about data hierarchies and navigation

Let us establish the formal semantics of one-to-many cardinality. Consider a binary relationship R between entity sets E₁ (the 'one' side) and E₂ (the 'many' side).

Definition:

A relationship R between entity sets E₁ and E₂ has one-to-many (1:N) cardinality if and only if:

1. For every entity e₁ ∈ E₁, there can exist zero or more entities e₂ ∈ E₂ such that (e₁, e₂) ∈ R
2. For every entity e₂ ∈ E₂, there exists at most one entity e₁ ∈ E₁ such that (e₁, e₂) ∈ R

This definition reveals the fundamental asymmetry: the constraint from E₂ to E₁ is strict (at most one), while the constraint from E₁ to E₂ is relaxed (unbounded).

Function Perspective:

We can view the relationship as:

f: E₂ → E₁    (partial function: each child → at most one parent)
g: E₁ → 𝒫(E₂)  (multifunction: each parent → set of children)

Where 𝒫(E₂) denotes the power set (all possible subsets) of E₂.

Key Properties:

This functional asymmetry directly determines the foreign key direction in relational mapping.

Reading 1:N Cardinality:

Given a relationship statement:

DEPARTMENT employs EMPLOYEES (1:N)

We read this as:

• ONE department can employ MANY employees
• Each employee is employed by at most ONE department

The 'one' side (DEPARTMENT) is the parent or referent. The 'many' side (EMPLOYEE) is the child or dependent.

Order matters in notation:

• 1:N with DEPARTMENT first means Department is the 'one' side
• N:1 would mean the same relationship but with reversed reading order
• Some texts use 1:M, 1:N, or 1:* interchangeably for this relationship

Always clarify which entity is on which side when communicating cardinality.

The one-to-many relationship appears across virtually every domain because hierarchical containment and ownership are fundamental organizational patterns. Let's examine various categories of 1:N relationships:

The Customer-Order Paradigm:

The CUSTOMER → ORDERS relationship is so paradigmatic that it serves as the canonical example in most database textbooks. Let's examine why:

CUSTOMER:
├── customer_id (PK)
├── name
├── email
└── ... (address, preferences, etc.)

ORDERS:
├── order_id (PK)
├── customer_id (FK) ──────→ References CUSTOMER
├── order_date
├── total_amount
└── status

Why is this 1:N?

1. A single customer can place many orders over time (no limit)
2. Each individual order is placed by exactly one customer (ownership is singular)
3. The 'many' side (ORDERS) carries the foreign key pointing to the 'one' side (CUSTOMER)

This pattern generalizes:

• The independent entity (CUSTOMER) exists regardless of children
• The dependent entity (ORDER) references its parent via foreign key
• Deleting the parent raises referential integrity questions (CASCADE? SET NULL? RESTRICT?)
• Querying 'all orders for customer X' is a common access pattern

One-to-many cardinality has distinct representations across different ER notation styles. Understanding these helps you read diagrams from various sources and communicate precisely with diverse teams.

Chen Notation Example:

[DEPARTMENT]────1────<EMPLOYS>────N────[EMPLOYEE]

Reading: Each department (1) employs many (N) employees. Each employee is employed by one (implied) department.

Crow's Foot Notation Example:

[DEPARTMENT] ||──────────⅄| [EMPLOYEE]

|| on left = one (mandatory)
⅄| on right = many (mandatory at least one)

Or with optionality:
[DEPARTMENT] ||──────────⅄o [EMPLOYEE]

⅄o = zero or many (optional participation)

The three-pronged 'crow's foot' symbol visually suggests 'branching out' to multiple entities.

Min-Max Notation Example:

[DEPARTMENT]──(1,1)──<EMPLOYS>──(0,N)──[EMPLOYEE]

Reading:

• Each EMPLOYEE participates in EMPLOYS with min=0, max=N → An employee may not be in any department (0) or can be in one department, and this relationship can involve many employees total (N)
• Wait—this isn't quite right. Let's be precise:

Correct interpretation: The (min,max) pair applies to how many times an entity participates in the relationship.

[DEPARTMENT]──(1,N)──<EMPLOYS>──(1,1)──[EMPLOYEE]

• From DEPARTMENT's perspective: Each department participates 1 to N times (has 1 to many employees)
• From EMPLOYEE's perspective: Each employee participates exactly once (works in exactly one department)

Note: Conventions vary! Some place (min,max) based on target cardinality, others based on source participation. Always verify the convention in your context.

UML Association Notation:

[Department]──────────[Employee]
      1                    *

Or with roles:
[Department] 1────employs────* [Employee]

• 1 = exactly one (or 1..1)
• * = zero or more (or 0..*)
• 1..* = one or more

UML's flexibility: UML allows precise multiplicity specifications:

• 0..1 = optional, at most one
• 1 = exactly one
• 0..* or * = zero or more
• 1..* = one or more (mandatory with no upper bound)
• 3..5 = between 3 and 5

This expressiveness exceeds basic ER notation, making UML popular for detailed specifications.

The relational mapping of 1:N relationships is remarkably straightforward and elegant: place a foreign key in the 'many' side table that references the primary key of the 'one' side table. This simple rule is the cornerstone of relational database design.

Basic Implementation:

-- Parent table (the 'one' side)
CREATE TABLE department (
    department_id   INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    location        VARCHAR(100)
);

-- Child table (the 'many' side)
CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department_id   INT,                          -- Foreign key column
    hire_date       DATE,
    salary          DECIMAL(10,2),
    FOREIGN KEY (department_id) 
        REFERENCES department(department_id)     -- FK constraint
);

Why FK on 'many' side?

Consider the alternative—putting the FK on the 'one' side:

-- WRONG: FK on 'one' side
CREATE TABLE department (
    department_id   INT PRIMARY KEY,
    name            VARCHAR(100),
    employee_id     INT REFERENCES employee(employee_id)  -- Problem!
);

This would allow each department to reference only ONE employee. To reference multiple employees, you'd need multiple rows or a comma-separated list—violating first normal form and making queries impractical.

The FK-on-many-side design allows:

• Multiple employees to share the same department_id value
• Each employee to have exactly one department_id (enforced by single column)
• Efficient indexing and JOIN operations

Referential Integrity Actions:

The foreign key constraint enforces referential integrity, but you must specify what happens when the referenced parent is deleted or updated:

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100),
    department_id   INT,
    FOREIGN KEY (department_id) REFERENCES department(department_id)
        ON DELETE SET NULL           -- Set FK to NULL when parent deleted
        ON UPDATE CASCADE            -- Update FK when parent PK changes
);

Available Actions:

Choosing the right action:

• CASCADE: When children have no meaning without parent (e.g., order items without order)
• SET NULL: When children can exist independently (e.g., employees when department dissolved)
• RESTRICT: When parent should never be deleted while children exist (safety default)

Cardinality (1:N) tells us the maximum number of associations. Participation constraints tell us the minimum—whether an entity is required to participate in the relationship or participation is optional.

Example: Department-Employee Variations

Scenario A: Employee must belong to a department (total participation)

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department_id   INT NOT NULL,              -- NOT NULL enforces total participation
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

Business rule: Every employee must be assigned to a department. Unassigned employees are not permitted.

Scenario B: Employee may be unassigned (partial participation)

CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department_id   INT,                       -- NULL allowed: partial participation
    FOREIGN KEY (department_id) REFERENCES department(department_id)
);

Business rule: Employees may exist without department assignment (e.g., contractors, new hires pending placement).

Complex Participation Scenarios:

Scenario C: Every department must have employees

This requires total participation on the 'one' side—every department must relate to at least one employee. SQL doesn't naturally support this:

-- Cannot directly express: "No department shall have zero employees"
-- Must use triggers or application logic

CREATE OR REPLACE FUNCTION check_department_has_employees()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM employee WHERE department_id = OLD.department_id
    ) AND TG_OP = 'DELETE' THEN
        -- Allow if this was the last employee being moved, not deleted
        IF EXISTS (SELECT 1 FROM department WHERE department_id = OLD.department_id) THEN
            RAISE EXCEPTION 'Department must have at least one employee';
        END IF;
    END IF;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

Scenario D: Both sides mandatory

• Every employee must have a department (FK NOT NULL)
• Every department must have employees (trigger/constraint)

This creates a chicken-and-egg problem: you can't insert a department without employees, but you can't insert employees without a department.

Solutions:

1. Use DEFERRABLE constraints (checked at transaction end)
2. Insert department, insert employees, then enable constraint
3. Design the workflow to always create both together

Diagrammatic Representation of Participation:

Crow's Foot Notation:

Total participation: ||──────⅄| (bar means mandatory)
Partial participation: |o──────⅄o (circle means optional)

Example - Employee must have department, department may have no employees:
[DEPARTMENT] |o──────────⅄| [EMPLOYEE]
             ↑              ↑
          optional       mandatory
        (dept can be    (emp must have
         empty)           a dept)

Chen Notation:

• Double lines indicate total participation
• Single lines indicate partial participation

[DEPARTMENT]────1────<EMPLOYS>════N════[EMPLOYEE]
                                  ↑
                          Double line = total

Min-Max Notation:

[DEPARTMENT]──(0,N)──<EMPLOYS>──(1,1)──[EMPLOYEE]
                                  ↑
                          min=1 means total

The (1,1) indicates: each employee participates AT LEAST once and AT MOST once.

One-to-many relationships generate characteristic query patterns. Understanding these patterns helps you write efficient queries and design appropriate indexes.

Pattern 1: Find all children for a parent

The most common pattern—given a parent, find all associated children:

-- Find all employees in the Engineering department
SELECT e.*
FROM employee e
JOIN department d ON e.department_id = d.department_id
WHERE d.name = 'Engineering';

-- Or, if you know the department_id:
SELECT * FROM employee WHERE department_id = 5;

Index recommendation: Index on employee.department_id for efficient lookup.

Pattern 2: Find the parent for a child

Given a child, find its parent:

-- Find the department for employee 'John Doe'
SELECT d.*
FROM department d
JOIN employee e ON d.department_id = e.department_id
WHERE e.name = 'John Doe';

Index recommendation: The FK column employee.department_id is already efficient for JOINs; ensure department.department_id (PK) is indexed (which it is by default).

Pattern 3: Aggregate children per parent

Count, sum, or otherwise aggregate children grouped by parent:

-- Count employees per department
SELECT d.name, COUNT(e.employee_id) as employee_count
FROM department d
LEFT JOIN employee e ON d.department_id = e.department_id
GROUP BY d.department_id, d.name;

-- Total salary per department
SELECT d.name, SUM(e.salary) as total_payroll
FROM department d
LEFT JOIN employee e ON d.department_id = e.department_id
GROUP BY d.department_id, d.name;

Note: Use LEFT JOIN to include departments with zero employees.

Pattern 4: Find parents with specific child characteristics

-- Departments with at least one employee earning > 100K
SELECT DISTINCT d.*
FROM department d
JOIN employee e ON d.department_id = e.department_id
WHERE e.salary > 100000;

-- Departments where ALL employees earn > 100K
SELECT d.*
FROM department d
WHERE NOT EXISTS (
    SELECT 1 FROM employee e
    WHERE e.department_id = d.department_id
      AND e.salary <= 100000
)
AND EXISTS (
    SELECT 1 FROM employee e
    WHERE e.department_id = d.department_id
);

Pattern 5: Hierarchical traversal

When 1:N models hierarchies (categories → subcategories), recursive queries become relevant:

-- Self-referential 1:N: employee → manager
CREATE TABLE employee (
    employee_id     INT PRIMARY KEY,
    name            VARCHAR(100),
    manager_id      INT REFERENCES employee(employee_id)
);

-- Find all subordinates under a manager (recursive CTE)
WITH RECURSIVE subordinates AS (
    -- Base: direct reports
    SELECT employee_id, name, manager_id, 1 as level
    FROM employee
    WHERE manager_id = 1  -- Starting manager
    
    UNION ALL
    
    -- Recursive: subordinates of subordinates
    SELECT e.employee_id, e.name, e.manager_id, s.level + 1
    FROM employee e
    JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM subordinates ORDER BY level;

Self-referential 1:N relationships are common for organizational charts, category trees, and graph structures stored as adjacency lists.

While 1:N relationships are fundamental and straightforward, performance requires attention as data scales. Here are key considerations:

Index Strategies:

-- Basic FK index
CREATE INDEX idx_employee_department 
    ON employee(department_id);

-- Composite index for common query patterns
CREATE INDEX idx_employee_dept_salary 
    ON employee(department_id, salary);

-- Covering index (includes all commonly selected columns)
CREATE INDEX idx_employee_dept_covering 
    ON employee(department_id) 
    INCLUDE (name, hire_date, salary);

Execution Plan Analysis:

-- Before index: Sequential Scan on employee
EXPLAIN SELECT * FROM employee WHERE department_id = 5;

-- After index: Index Scan using idx_employee_department
EXPLAIN SELECT * FROM employee WHERE department_id = 5;

The difference at scale: scanning 1M rows vs. looking up ~100 rows via index.

Cascade Delete Performance:

When deleting a parent with many children, CASCADE DELETE can be slow:

-- This might delete thousands of orders when customer is deleted
DELETE FROM customer WHERE customer_id = 123;
-- With ON DELETE CASCADE, all orders for customer 123 are also deleted

For large cascades:

• Consider batching (delete children first in batches, then parent)
• Use soft deletes (mark as deleted rather than physical deletion)
• Archive before deletion if audit trail is needed

We have comprehensively explored one-to-many cardinality—the workhorse of relational database design. Let's consolidate the essential takeaways:

What's Next:

With 1:1 and 1:N cardinalities mastered, we now turn to the most flexible—and complex—cardinality type: many-to-many (M:N). This relationship requires an intermediate table (junction/bridge table) and introduces new design considerations around composite keys and relationship attributes.