Identify One-to-Many (1:N) Relationships

A one-to-many (1:N) relationship exists when one instance of entity A can be related to zero, one, or many instances of entity B, while each instance of B is related to at most one instance of A. This lesson shows how to recognize 1:N in an ER diagram, how to confirm it from business rules, and how to implement it with keys.

How to recognize 1:N at a glance

1. Foreign-key pattern (table view)
   The table on the “many” side contains a foreign key (FK) referencing the primary key (PK) of the “one” side.
   • CUSTOMER(CustomerID) ⟶ one
   • ORDER(OrderID, CustomerID FK→Customer.CustomerID) ⟶ many
2. Domain logic (business rule)
   Ask: “Can one A be associated with many B, and must each B belong to at most one A?”
   • One Department has many Employees, each Employee works in one Department.
   • One Vendor supplies many Products, each Product comes from one Vendor.
3. Diagram cues (Crow’s Foot notation)
   A straight bar | on the “one” side and a crow’s foot << on the “many” side. Optionality is shown with a small circle O.

   DEPARTMENT  |──────────<<  EMPLOYEE

   |=mandatory, O=optional.
4. Cardinality labels (textual notation)
   

   CUSTOMER (1) ───── <──── (0..*) ORDER

   Read as: one Customer can have zero or many Orders; each Order has exactly one Customer.

Summary indicators

Indicator	Meaning
Foreign Key	FK resides on the many-side; references one-side PK
Crow’s Foot	Three prongs at the many-side; bar/circle at the one-side
Cardinality	1 to 0..*, or 1 to N
Business rule	One A can own/contain many B; each B belongs to one A

Implement by placing the parent’s PK in the child as a FK. Add an index on the FK for join performance.

-- Parent (one)
CREATE TABLE Department (
  DeptID      INT PRIMARY KEY,
  DeptName    VARCHAR(100) NOT NULL
);

-- Child (many)
CREATE TABLE Employee (
  EmpID       INT PRIMARY KEY,
  EmpName     VARCHAR(100) NOT NULL,
  DeptID      INT NOT NULL,
  CONSTRAINT fk_employee_dept
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

-- Helpful for joins:
CREATE INDEX ix_employee_deptid ON Employee(DeptID);
      

• Where does the FK go? In the table on the many side.
• Optional vs mandatory
  • Optional child: allow DeptID to be NULL (0..*).
  • Mandatory child: set DeptID NOT NULL (1..*).
• Delete behavior: choose RESTRICT (safest), or CASCADE if child rows should be removed with the parent.

1. Customer ⟶ Order
   A customer may place many orders; each order is from one customer.
2. ProjectManager ⟶ Project (max 3)
   Add a business rule limiting projects per manager (enforced in application or via constraints/triggers).
3. Vendor ⟶ Product
   Each product references exactly one vendor via VendorID.

A many-to-one (N:1) view is the same relationship read from the other side. Implementation does not change—the FK remains on the many side.

When you have two entities A and B, ask:

1. How many B can belong to one A?
2. How many A can one B belong to?

If the answers are “many” for #1 and “one (or none)” for #2, you’re looking at a 1:N relationship.