One-to-Many Relationship: ER Diagram and SQL Example

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 at most one row in Table A. Business rules determine whether that parent is required or optional.

Customer (one) ||----------o{ SalesOrder (zero or many)

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 NOT NULL PRIMARY KEY,
  Name         VARCHAR(100) NOT NULL
);

-- Child (the "many" side)
CREATE TABLE SalesOrder (
  OrderID      INT NOT NULL 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)
);

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

Supply identifiers explicitly. SalesOrder.CustomerID is deliberately not unique: different orders must be able to reference the same customer. A foreign key prevents nonexistent customer references; NOT NULL makes the reference mandatory. Neither constraint requires every customer to have an order.

This example omits an explicit delete action. Adapt deletion policy and syntax to your DBMS; in this schema, deleting a referenced customer is prevented while orders still reference it.

• 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: Consider an index on the child FK column for joins and parent-row checks. Check existing indexes and workload before adding one; a foreign key does not universally create that index automatically.

• 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.
• Assuming the FK is automatically indexed. Inspect the schema and query plans; the benefit of an index depends on data volume and workload.

The first diagram follows a customer to their orders. The second follows each order back to its customer. Both use the join condition SalesOrder.CustomerID = Customer.CustomerID. The match does not depend on visual row position or on OrderID equaling CustomerID.

The row mappings illustrated in both diagrams

SalesOrder.OrderID (PK)	SalesOrder.CustomerID (FK)	Referenced customer
5001	101	Ava
5002	101	Ava
5003	101	Ava
5004	103	Cara

OrderID identifies an individual order. CustomerID may repeat in SalesOrder, allowing several orders for one customer. Its foreign key and NOT NULL constraint require each order to reference an existing customer. No order row is required for Ben; optional participation on the customer side is represented by the absence of an order, not a null customer identifier in an order.

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.