One-to-many Relationships

A one-to-many (1:N) relationship exists when:

1. For one instance of Entity A, there can be zero, one, or many instances of Entity B.
2. For one instance of Entity B, there can be at most one instance of Entity A.

Think in terms of tables: one row in Table A can relate to many rows in Table B, while each row in Table B relates back to exactly one row in Table A.

Ask two questions about the pair of entities A and B:

1. How many B can be associated with one A? If the answer is “many (including zero),” that supports 1:N.
2. How many A can be associated with one B? If the answer is “exactly one (or at most one),” that completes 1:N.

Examples:
Customer → Order (one customer, many orders; each order belongs to exactly one customer),
Department → Employee (one department, many employees; each employee belongs to one department),
Author → Book (one author, many books; each book has one primary author) — for co-authors, see M:N in a later lesson.

1:N is implemented by placing a foreign key on the “many” side:

-- Parent (the "one" side)
CREATE TABLE Customer (
  CustomerID   INT PRIMARY KEY,
  Name         VARCHAR(100) NOT NULL
);

-- Child (the "many" side)
CREATE TABLE "Order" (
  OrderID      INT PRIMARY KEY,
  CustomerID   INT NOT NULL,                -- each order must belong to exactly one customer
  OrderDate    DATE NOT NULL,
  CONSTRAINT fk_order_customer
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
    ON DELETE RESTRICT                         -- choose CASCADE/SET NULL/RESTRICT per policy
);

-- Performance best practice:
CREATE INDEX ix_order_customerid ON "Order"(CustomerID);

• Optional vs. mandatory: Make the child FK NOT NULL to require participation (every B must have an A). Allow NULL for optional participation.
• Integrity: Select an ON DELETE action that matches business rules (e.g., CASCADE to remove children with the parent, RESTRICT/NO ACTION to prevent deletion if children exist).
• Indexing: Index the child FK column to accelerate joins and enforcement.

• E-commerce: Customer → Orders; Order → OrderLines.
• Content: Post → Comments; Category → Posts.
• Operations: Department → Employees; Manufacturer → Products.

• Storing lists in a single column (e.g., “phones = ‘555-1234,555-9876’”). This violates 1NF and blocks indexing/joins. Use a child table instead.
• Repeating columns (e.g., Phone1, Phone2, Phone3). Use a child table with one row per phone number.
• Forgetting the child FK index — causes slow joins and constraint checks.

The first diagram shows the relationship from A’s perspective: one A points to many Bs. The second shows B’s perspective: each B points back to exactly one A. Together they illustrate the two sides of the same 1:N relationship.

1. How many B can belong to one A? (Expect: many or zero)
2. How many A can one B belong to? (Expect: exactly one or at most one)

If your answers match, you have a one-to-many.

One-to-many is the most common relationship in relational design. Model it by placing a foreign key on the “many” side, choose optionality and delete actions that mirror the business rules, and index the FK. The next lesson explores one-to-many relationships further.