How to Identify One-to-Many Relationships in Database Design

A one-to-many (1:N) relationship allows one instance of A to relate to several instances of B, while each B relates to at most one A. Read the business rule in both directions before choosing the relationship. The maximum determines whether a side is one or many; the minimum determines whether participation is optional or required.

In Lesson 6's customer/order example, a customer may place several orders, but each order belongs to exactly one customer. This lesson applies the same reasoning to departments, project managers, and vendors, then shows where the foreign key belongs.

Ask Two Questions, Then Check Participation

1. For one A, how many B instances are permitted? A customer may place several orders. The maximum on the order side is many.
2. For one B, how many A instances are permitted? Each order belongs to one customer in this model. The maximum on the customer side is one.
3. May either exist without the other? A customer may have no orders yet, but a stored order must reference a customer. These are separate minimum-participation rules.
4. What does one row represent? Current assignments and assignment history can have different limits. Define that scope before interpreting the cardinalities.

Use the permitted maximum rather than today's row count. One order for every customer today does not establish a one-to-one rule if the application must accept another order tomorrow.

Indicators in a Diagram or Schema

A foreign key by itself is not sufficient evidence: inspect its uniqueness and the business rule too. The numerical labels in the figures below state multiplicities directly; see Crow's Foot notation for the corresponding endpoint symbols.

The vendor rule is a deliberate simplification. If the same product can be supplied by several vendors, Vendor and Product have an M:N association, usually represented by a VendorProduct table. A vendor's price or delivery lead time may then belong to that association rather than Product alone.

Department to Employee is 1:N. Employee to Department is N:1. Reversing the reading direction changes the statement, not the tables or the foreign key's location.

Match rows using Employee.DeptID = Department.DeptID. An employee's identifier does not have to equal the department identifier, and the parent key is not moved out of Department. Employee stores a reference to its value.

Each employee must belong to an existing department; a department may exist before any employee is assigned. Supply the identifiers explicitly in this example.

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

CREATE TABLE Employee (
  EmpID INT NOT NULL PRIMARY KEY,
  EmpName VARCHAR(100) NOT NULL,
  DeptID INT NOT NULL,
  CONSTRAINT fk_employee_dept
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

-- Consider this index for joins and parent-row checks.
CREATE INDEX ix_employee_deptid ON Employee(DeptID);

• PRIMARY KEY: EmpID identifies an employee; DeptID identifies a department in its own table.
• FOREIGN KEY: Employee.DeptID must reference a department that exists.
• NOT NULL: Every stored employee must supply a department reference.
• No uniqueness on Employee.DeptID: Multiple employees may reference the same department.

Do Not Confuse the Two Minimums

Changing Employee.DeptID to allow nulls permits an employee without a department. It changes that employee's participation to zero or one department. It does not determine whether a department must have employees.

Conversely, a non-null foreign key still allows an empty department, as department 20 illustrates. Requiring at least one employee for every department needs additional enforcement appropriate to the DBMS and transaction rules. It is not established by making the employee reference non-null.

Review mandatory and optional participation when deciding these minimums. Choose deletion behavior separately: rejecting deletion of a referenced department, reassigning its employees, or deleting them represents different business policies. This example omits explicit referential actions; adapt their syntax to your DBMS. Check existing indexes and query plans before adding another index.

“A manager may manage up to three active projects” is a bounded 1:N rule. Three is greater than one, so this is not 1:1. A foreign key validates each project's manager, but it does not count how many projects reference that manager.

For a table containing only current active projects, one concrete design gives each manager three reusable slots:

CREATE TABLE ProjectManager (
  ManagerID INT NOT NULL PRIMARY KEY,
  ManagerName VARCHAR(100) NOT NULL
);

CREATE TABLE Project (
  ProjectID INT NOT NULL PRIMARY KEY,
  ManagerID INT NOT NULL,
  ManagerSlot INT NOT NULL,
  CONSTRAINT fk_project_manager
    FOREIGN KEY (ManagerID) REFERENCES ProjectManager(ManagerID),
  CONSTRAINT ck_project_slot CHECK (ManagerSlot BETWEEN 1 AND 3),
  CONSTRAINT uq_manager_slot UNIQUE (ManagerID, ManagerSlot)
);

The range check permits only three slot values. NOT NULL prevents an assignment from bypassing the slot rule with a missing value. Uniqueness prevents two projects from using the same manager's slot. Together they permit at most three current project rows per manager. Different managers can reuse the same slot numbers.

With manager 7 occupying slots 1, 2 and 3, another row for manager 7 with slot 4 fails the check; another row using slot 2 fails uniqueness. A manager with no projects is still allowed. A project reassignment must use a free slot for the new manager.

This simplified design counts every row in Project as active. Keeping completed projects in that same table would continue consuming slots. If history is required, separate current assignments from their history or design enforcement around the actual active-period rule. More general limits may require DBMS-specific enforcement and concurrency handling.

1. Can employees 101 and 102 both have DeptID 10? Yes. The repeated department reference is what allows several employees per department.
2. Does Employee.DeptID NOT NULL require department 20 to have an employee? No. It requires a department for every employee row that exists.
3. Is a maximum of three projects per manager one-to-one? No. The allowed maximum exceeds one.
4. Where is the foreign key when you read Employee to Department as N:1? It remains in Employee.