Cardinality & Participation

Master how to specify exact relationship rules between entities, define mandatory vs optional participation, and translate business constraints into precise ER diagrams for BookVault's database.

Understanding Cardinality

Cardinality tells us exactly how many instances of one entity can relate to another entity. Think of it as the numerical rules that govern relationships.

Most database designers know about one-to-many relationships. But cardinality goes deeper. It specifies the minimum and maximum number of relationships each entity instance can have.

Written as (min, max), cardinality appears next to entities in ER diagrams. A customer who must place at least one order but can place many would be marked as (1, N).

BookVault Cardinality Examples

This diagram shows critical business rules. A customer (0,N) can exist without placing orders, but can place unlimited orders. Every order (1,1) must belong to exactly one customer.

Why does this matter? Because it prevents data disasters. Amazon doesn't allow orders without customers. Netflix doesn't allow viewing history without user accounts. These aren't just technical rules — they're business requirements.

Participation Constraints

Participation defines whether an entity must participate in a relationship. Two types exist: total and partial.

Spotify uses total participation between playlists and users — every playlist must belong to a user. But partial participation exists between songs and playlists — songs can exist without being in any playlist.

Complex BookVault Relationships

This three-way relationship shows sophisticated constraints. Orders have total participation with order_items — empty orders aren't allowed. But books have partial participation — they can exist without being ordered yet.

Common Cardinality Patterns

Uber uses (1,1) between trips and drivers — every trip needs exactly one driver. But (0,N) between drivers and trips — drivers can be offline with no active trips.

Implementing Cardinality in SQL

Cardinality constraints become NOT NULL constraints, foreign keys, and CHECK constraints in SQL. Here's how BookVault enforces its business rules:

-- Enforce (1,1) cardinality: Order must have exactly one customer
-- Every order.customer_id is required and references exactly one customer
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,  -- Enforces minimum cardinality of 1
    order_date DATE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Table 'orders' created successfully.
Constraint: customer_id cannot be NULL
Constraint: customer_id must reference existing customer

-- Enforce (1,N) cardinality: Order must have at least one item
-- Use CHECK constraint to ensure orders have items
CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT CHECK (quantity > 0),  -- Business rule: positive quantities
    unit_price DECIMAL(8,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Additional constraint: Orders must have at least one item
-- This requires a trigger or application-level validation

Table 'order_items' created successfully.
Constraint: quantity must be greater than 0
Constraint: order_id and book_id cannot be NULL
Foreign key constraints created

Real-World Cardinality Decisions

Different companies make different cardinality choices for similar relationships. These decisions reflect business strategy:

BookVault faces similar decisions. Should customers exist without orders? Yes — for marketing and future sales. Should orders exist without items? No — empty orders are meaningless.

Stripe's payment system demonstrates perfect cardinality modeling. Payments (1,1) must have exactly one customer and one payment method. But customers (0,N) can exist without payments — perfect for onboarding.

Testing Your Cardinality Rules

Good cardinality rules prevent bad data. Here's how to test BookVault's constraints:

-- Test 1: Try to create order without customer (should fail)
-- This violates (1,1) cardinality between orders and customers
INSERT INTO orders (order_id, order_date, status, total_amount) 
VALUES (1001, '2024-01-15', 'pending', 49.99);

-- Test 2: Try to create valid order with customer (should succeed)
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount) 
VALUES (1002, 1, '2024-01-15', 'pending', 49.99);

ERROR: Column 'customer_id' cannot be null
1 row(s) affected: Order 1002 created successfully

Honestly, most database problems come from weak cardinality rules. Orders without customers, products without categories, users without email addresses — all preventable with proper constraints.

Quiz