One-to-Many Relationship

Introduction

A one-to-many relationship in a database means that one record in one entity can be associated with many records in another entity, while each record on the many side is associated with one record on the one side according to the business rule. This is one of the most common and most important relationship types in relational database design.

A customer can place many orders. A department can have many employees. A category can contain many products. A blog post can have many comments. An account can have many transactions. A course can have many lessons. These are all one-to-many relationships when each child record belongs to one parent record.

CUSTOMER
    1
    |
    N
ORDER

Meaning:
One customer can place many orders.
Each order belongs to one customer.

In relational databases, a one-to-many relationship is normally implemented by placing a foreign key on the many side. The parent table has the primary key. The child table stores the parent's key as a foreign key. The foreign-key value is allowed to repeat because many child rows can reference the same parent row.

Basic One-to-Many Concept

Suppose a company database has Department and Employee. The business rule says one department can contain many employees, and each employee belongs to one department. Department is the one side. Employee is the many side. The relationship is Department 1:N Employee.

DEPARTMENT
    1
    |
    N
EMPLOYEE

IT Department:
  - John
  - Alice
  - David

HR Department:
  - Maria
  - Robert

The same idea applies to Customer and Order. One customer can have many order records. Customer 101 may place orders 5001, 5002, and 5003. The customer is stored once in the customer table. Each order row stores customer_id 101 to show who placed the order.

This avoids repeated parent data. We do not need to copy the customer's name, email, phone number, and address into every order row unless there is a specific historical reason. The relationship lets one stored customer row connect to many order rows.

Real-World Examples

One-to-many relationships appear almost everywhere in business systems. A customer has many orders. A department has many employees. A category has many products. A country has many cities. An author has many articles. A blog post has many comments. An order has many order items. A project has many tasks. An account has many transactions. A course has many lessons.

The actual relationship always depends on requirements. Product and Category may be one-to-many in a simple catalog where each product belongs to one category. In a marketplace where one product can appear under multiple categories, Product and Category become many-to-many. Employee and Department may be one-to-many in one company, but many-to-many in another if employees can work across multiple departments.

This is why relationship design must be driven by business rules. Do not decide cardinality based on a generic example. Ask how the application must behave and what the data must represent.

One Side and Many Side

In Customer 1:N Order, Customer is the one side and Order is the many side. One customer row can be referenced by many order rows. In Department 1:N Employee, Department is the one side and Employee is the many side. One department row can be referenced by many employee rows.

The one side normally owns the identifier that the many side references. The many side repeats that identifier across many rows. This repetition is expected and correct in a one-to-many relationship.

Database Representation

Consider customer and order data. The customers table stores each customer once. The orders table stores each order separately and includes customer_id to identify the customer who placed it.

Customer 101 appears once in the customers table and three times as a foreign-key value in the orders table. That does not mean the customer row is duplicated. It means three child records reference the same parent record.

Primary Key on the One Side

The one-side table normally has a primary key that uniquely identifies each parent row. In the customer example, customer_id uniquely identifies each customer. In the department example, department_id uniquely identifies each department. In the order example, order_id uniquely identifies each order when order is the parent of order items.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

The primary key is what child rows reference. It should be unique, non-null, stable, and suitable for identifying the parent record. If parent identifiers change frequently, all related child references become harder to manage. This is one reason database designs often use stable surrogate keys such as customer_id, order_id, and department_id.

Foreign Key on the Many Side

The many-side table contains the foreign key. For Customer 1:N Order, orders.customer_id references customers.customer_id. For Department 1:N Employee, employees.department_id references departments.department_id. For Order 1:N Order Item, order_items.order_id references orders.order_id.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(30),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The most important rule is simple: in a one-to-many relationship, the foreign key goes on the many side. The one side has the primary or candidate key. The many side stores that key as a foreign key. Because many rows can belong to the same parent, the foreign-key value can repeat.

ONE-TO-MANY

ONE side:
Primary Key

MANY side:
Foreign Key

Why the Foreign Key Goes on the Many Side

The foreign key goes on the many side because one parent can have an unlimited number of child rows without changing the table structure. Suppose department 10 contains employees 101, 102, and 103. Each employee row stores department_id 10. If the department later has 500 employees, the employees table simply has 500 rows referencing department 10.

employee_id | employee_name | department_id
------------------------------------------
101         | John          | 10
102         | Alice         | 10
103         | David         | 10

The wrong design would store employee1_id, employee2_id, and employee3_id inside the department table. That creates a fixed number of employees, repeating columns, difficult queries, difficult updates, many NULL values, and poor normalization. If a fourth employee joins, the table structure itself may need to change. That is not relational design.

Poor:
DEPARTMENT(department_id, employee1_id, employee2_id, employee3_id)

Correct:
DEPARTMENT(department_id, department_name)
EMPLOYEE(employee_id, employee_name, department_id)

Parent and Child Terminology

In a foreign-key relationship, the referenced table is commonly called the parent table, and the referencing table is commonly called the child table. For Customer and Order, Customer is the parent because customers.customer_id is referenced. Order is the child because orders.customer_id references the customer.

Parent and child here describe database dependency, not object-oriented inheritance. A child table is not a subclass of the parent table. It simply stores a foreign key that points to a parent row. The terminology helps explain referential integrity, insert order, delete behavior, and cascade rules.

Parent:
CUSTOMERS(customer_id PK)

Child:
ORDERS(order_id PK, customer_id FK)

One-to-Many vs Many-to-One

One-to-many and many-to-one describe the same relationship from opposite directions. From Customer to Order, the relationship is one-to-many because one customer can have many orders. From Order to Customer, the relationship is many-to-one because many orders can belong to one customer.

Both descriptions are useful. Business users may say customers place orders. Developers may say each order references one customer. Analysts may say orders are grouped by customer. These are different views of the same relationship.

Customer -> Orders
1:N

Orders -> Customer
N:1

When designing a database, always analyze both directions. How many orders can one customer have? Many. How many customers can one order belong to? One. Therefore the relationship is Customer 1:N Order.

Customer-to-Order Example

A customer-to-order relationship is one of the clearest examples. The business rule says a customer can place many orders, and each order belongs to one customer. The customer table stores customer information. The order table stores order information and the customer_id foreign key.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(30),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

Multiple orders can contain customer_id 101. That is correct because customer_id is the relationship column, not the order identifier. The order_id still uniquely identifies each order. The foreign-key value repeats; the primary-key value does not.

order_id | customer_id
----------------------
5001     | 101
5002     | 101
5003     | 101
5004     | 102

Foreign Key Values Can Repeat

Repeated foreign-key values are a critical characteristic of one-to-many relationships. If you see customer_id 101 in three order rows, that does not mean the orders table is wrong. It means three orders belong to the same customer. If you see department_id 10 in ten employee rows, that means ten employees belong to department 10.

For a normal one-to-many relationship, the foreign-key column should usually not be declared UNIQUE. If orders.customer_id is UNIQUE, customer 101 can appear only once in orders. That changes the relationship from one-to-many to maximum one child per parent, which behaves like one-to-one from the parent side.

Wrong for normal Customer 1:N Order:
customer_id INT UNIQUE

Correct for normal Customer 1:N Order:
customer_id INT NOT NULL

Use UNIQUE on a foreign key only when the business rule truly requires maximum one child row per parent. Otherwise, let the foreign-key value repeat.

Department-to-Employee Example

In a company database, one department can employ many employees, and every employee may be required to belong to one department. The department table stores department_id and department_name. The employee table stores employee_id, employee_name, and department_id.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

If department 10 is IT, employee rows for John, Alice, and David can all contain department_id 10. The database stores IT once in departments and references it from many employee rows. This avoids repeatedly storing the department name on every employee record.

Category-to-Product Example

A category-to-product relationship is one-to-many when each product belongs to one category. One category can contain many products. The products table stores category_id as a foreign key.

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(150) NOT NULL,
    category_id INT NOT NULL,
    FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
);

If requirements later say a product can belong to multiple categories, this design may no longer fit. The relationship becomes many-to-many and normally requires a product_category junction table. This shows why requirement clarity matters before choosing a relationship type.

Order-to-Order Item Example

Order to Order Item is one of the most important database examples. One order contains many line items. Each order item belongs to one order. The order_items table contains order_id as a foreign key. It may also contain product_id, quantity, unit_price, discount, and tax details.

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
);

Order 5001 can have several order item rows. Each row represents one line item. The relationship lets an order contain any number of items without creating columns such as product1, product2, and product3 on the order table.

Order 5001
  - Item 1: product 100, quantity 2
  - Item 2: product 200, quantity 1
  - Item 3: product 300, quantity 5

Other Practical Examples

Banking systems commonly use Account 1:N Transaction. One account may have many deposits, withdrawals, purchases, fees, interest entries, reversals, and adjustments. Each transaction belongs to one account in the simplest account-ledger model.

Content systems commonly use Blog Post 1:N Comment. One blog post can have many comments. Each comment belongs to one post. Project management systems commonly use Project 1:N Task. One project can contain many tasks. Each task belongs to one project.

Geographic models can use Country 1:N City if each city belongs to one country. A larger model may chain one-to-many relationships: Country has many States, State has many Cities, City has many Customers, and Customer has many Orders. Relationship chains allow complex structures to be represented as connected tables.

Cardinality and Optionality

One-to-many describes maximum cardinality, but a complete relationship also needs minimum cardinality. A customer may place zero or many orders. That is Customer 1 to 0..many Orders. Every order may be required to belong to exactly one customer. That is Order to exactly one Customer.

CUSTOMER
  1
  |
  0..*
ORDER

Customer can have zero or many orders.
Order must belong to one customer.

Another relationship may require at least one child. For example, every order must contain at least one order item. Conceptually, Order has one or more Order Items. A simple foreign key can ensure every order item references a valid order, but it does not automatically ensure every order has at least one item. That rule may require transaction logic, deferred validation, triggers, or application/service validation depending on the database and design.

Optionality on the many side is controlled by the foreign-key column. If every employee must belong to a department, employees.department_id should be NOT NULL. If employees may exist before department assignment, department_id may allow NULL. The choice must come from business requirements.

Referential Integrity

Foreign keys protect one-to-many relationships through referential integrity. If orders.customer_id is 999 but customer 999 does not exist, the order is orphaned. A foreign-key constraint can reject this invalid data. This is important because applications, imports, scripts, jobs, and manual database operations can all create data; the database should protect core relationship rules.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The valid insert workflow usually creates the parent first and then creates the child. Insert customer 101, then insert order 5001 with customer_id 101. If you try to insert an order for customer 999 before customer 999 exists, the foreign key should reject the insert.

INSERT INTO customers (customer_id, name)
VALUES (101, 'John');

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (5001, 101, '2026-09-01');

-- Invalid if customer 999 does not exist:
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (5002, 999, '2026-09-02');

Parent Delete Behavior

Parent deletion is one of the most important design questions in one-to-many relationships. Suppose customer 101 has three orders. What should happen if someone tries to delete customer 101? The answer depends on the business rules and the configured referential action.

Common referential actions include RESTRICT or NO ACTION, CASCADE, SET NULL, and SET DEFAULT. RESTRICT or NO ACTION generally prevents the parent from being deleted while child rows still reference it. CASCADE automatically deletes related child rows. SET NULL changes the child foreign key to NULL if the parent is deleted. SET DEFAULT assigns a default value where supported.

Customer 101
  - Order 5001
  - Order 5002
  - Order 5003

DELETE FROM customers WHERE customer_id = 101;

Possible outcomes:
Rejected
Cascade child delete
Set child customer_id to NULL
Set child customer_id to default

Cascade should be used carefully. Deleting an order and cascading to order items may be reasonable because order items have little meaning without the order. Deleting a customer and cascading to orders, payments, invoices, and audit records may be legally or operationally unacceptable. Referential actions should reflect business and compliance rules, not convenience.

One-to-Many and JOINs

Relationships are frequently traversed using JOINs. A Customer-Order relationship can be queried by joining customers.customer_id to orders.customer_id. The result repeats parent data once for each matching child row. If John has three orders, John may appear three times in the joined result. That does not necessarily mean duplicate customer data exists; it means one parent matched three children.

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.order_date
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id;

An INNER JOIN returns only parents with matching children. If a customer has no orders, that customer does not appear. A LEFT JOIN returns all customers and shows NULL for order columns when no matching order exists. Optionality should guide the query choice.

SELECT
    c.customer_id,
    c.name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

Counting Children and Finding Missing Children

One-to-many relationships are often used for aggregation. A common question is: how many orders has each customer placed? Use a LEFT JOIN when customers with zero orders should be included, and COUNT the child key.

SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.name;

To find parents with no children, use a LEFT JOIN and filter where the child key is NULL, or use NOT EXISTS. For example, customers who have never placed an order can be found by looking for no matching order rows.

SELECT
    c.customer_id,
    c.name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

The NOT EXISTS version is also common and often clear because it directly expresses that no child row exists for the parent.

SELECT
    c.customer_id,
    c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Row Multiplication in Multi-Table JOINs

One-to-many relationships explain why rows multiply in query results. If one customer has two orders and each order has three order items, a join across Customer, Order, and Order Item may produce six rows. This is not automatically wrong. It is the natural result of joining across two one-to-many relationships.

CUSTOMER
   |
   1:N
   |
ORDER
   |
   1:N
   |
ORDER_ITEM

Row multiplication becomes dangerous when developers aggregate at the wrong level. Suppose an order has total_amount 100 and three order items. If a query joins orders to order_items and then sums total_amount, the order total may be counted three times. Many SQL reporting bugs are caused by misunderstanding relationship cardinality.

Before writing complex SQL, ask which table is on the one side, which table is on the many side, whether child records can be missing, and whether multiple matches can occur. Correct query design starts with correct relationship understanding.

Foreign Key Indexing

Foreign-key columns are commonly used in joins, parent-child lookups, filtering, and referential checks. For example, an application may frequently retrieve all orders for a customer with WHERE customer_id = 101. An index on orders.customer_id may help depending on workload, data distribution, query patterns, and the database engine.

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Do not confuse a foreign key with an index. A foreign key primarily enforces referential integrity. An index primarily supports data access performance. Some database systems automatically create or require supporting indexes in certain cases, while others do not automatically index every foreign key. Always understand the behavior of the RDBMS being used.

Composite Foreign Keys

Some relationships reference a composite key. If the parent table is identified by multiple columns, the child table must store all required columns to reference that parent. Composite keys are common in junction tables, historical tables, and some domain-specific designs.

CREATE TABLE departments (
    company_id INT NOT NULL,
    department_id INT NOT NULL,
    department_name VARCHAR(100),
    PRIMARY KEY (company_id, department_id)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    company_id INT NOT NULL,
    department_id INT NOT NULL,
    employee_name VARCHAR(100),
    FOREIGN KEY (company_id, department_id)
        REFERENCES departments(company_id, department_id)
);

Here, department_id alone may not be globally unique. The combination of company_id and department_id identifies the department. The employee table therefore needs both values to define the relationship.

Relationships and Normalization

One-to-many relationships are central to normalization. Instead of repeating parent data in every child row, the parent data is stored once and child rows reference it. This reduces redundancy and helps prevent update anomalies.

Without a separate Customer table, each order might repeat customer name and email. If the email changes, every order row must be updated. If one row is missed, the database contains conflicting customer details. With a normalized relationship, customer email is stored once in Customer, and all orders reference customer_id.

Poor:
ORDER(order_id, customer_name, customer_email, order_date)

Better:
CUSTOMER(customer_id, name, email)
ORDER(order_id, customer_id, order_date)

Relationships do not mean related rows are physically stored together. The relationship is logical, based on matching key values. Physical storage, indexes, pages, and buffers are separate internal concerns handled by the database engine.

When One-to-Many Becomes Many-to-Many

Requirements can change a relationship. Suppose the first version of a system says each employee belongs to one project. That can be modeled as Project 1:N Employee with project_id in employees. Later, the business says employees can work on multiple projects and each project can have many employees. The relationship is now many-to-many.

Old:
PROJECT 1:N EMPLOYEE
employees.project_id

New:
EMPLOYEE N:M PROJECT

Resolved as:
EMPLOYEE 1:N EMPLOYEE_PROJECT N:1 PROJECT

Do not force a many-to-many requirement into a one-to-many schema. If an employee can participate in multiple projects, a single project_id column in employees is not enough. It allows only one project per employee and contradicts the business rule.

Historical One-to-Many Relationships

Some one-to-many relationships change over time. An employee may currently belong to one department, but over five years that employee may have belonged to several departments. If the table stores only employees.department_id, the system knows the current department but loses the history.

If the requirement asks which department an employee belonged to last year, a history table is needed. The relationship becomes temporal, with effective_from and effective_to dates.

EMPLOYEE_DEPARTMENT_HISTORY
---------------------------
employee_id
department_id
effective_from
effective_to

History requirements often change the design. What looked like a simple current-state attribute can become a separate relationship table when the business needs past assignments, auditing, or reporting over time.

One-to-Many in APIs and ORM Systems

Application APIs often reflect one-to-many relationships. An endpoint such as GET /customers/101/orders represents Customer 1:N Order. The backend commonly queries orders where customer_id equals 101. A nested API response may show one customer object with an array of orders.

{
  "customerId": 101,
  "name": "John",
  "orders": [
    { "orderId": 5001 },
    { "orderId": 5002 },
    { "orderId": 5003 }
  ]
}

Object-relational mapping tools often model this as Customer having a collection of Order objects and Order having one Customer reference. The object model may say customer.getOrders(), but the relational implementation still usually depends on orders.customer_id as the foreign key.

Relationship Testing

Testing a one-to-many relationship should verify that the parent can be created, multiple children can reference one parent, a child cannot reference a nonexistent parent, a mandatory foreign key rejects NULL, parent deletion follows the expected rule, and joins return the correct related records.

-- Multiple child rows should be allowed
INSERT INTO orders VALUES (5001, 101, '2026-09-01');
INSERT INTO orders VALUES (5002, 101, '2026-09-02');
INSERT INTO orders VALUES (5003, 101, '2026-09-03');

-- Invalid parent should fail
INSERT INTO orders VALUES (5004, 999, '2026-09-04');

Testers should also check parent deletion behavior. If customer 101 has three orders, deleting customer 101 should either be rejected, cascade to children, set child keys to NULL, or follow another configured rule. The expected result must match the business requirement.

Common Mistakes

The most common mistake is placing the foreign key on the wrong side. A customer table with order_id is wrong when customers can have many orders because it allows only one order_id value per customer row. The correct design places customer_id in the orders table.

Another common mistake is adding UNIQUE to a normal one-to-many foreign key. If orders.customer_id is unique, one customer can have only one order. That breaks the relationship unless the business rule truly requires one order maximum.

Repeating columns such as order1, order2, and order3 are also poor design. New child records should become new rows, not new columns. Comma-separated child IDs are similarly weak because they are hard to query, hard to validate, hard to index, and cannot be protected with normal foreign keys.

Duplicating parent data in child rows is another frequent issue. Do not duplicate customer name and email in every order row unless the duplicate values are intentional historical snapshots. Avoid assuming every 1:N relationship is mandatory. Customer to Orders may be zero-or-many, while Order to Order Item may be one-or-more. Minimum cardinality matters.

Design Questions

Before creating a one-to-many relationship, ask clear questions. What is the parent entity? What is the child entity? Can one parent have multiple children? Can each child belong to only one parent? Can the parent have zero children? Can the child exist without a parent? Does the relationship change over time? What happens when the parent is deleted? Should the foreign key be indexed? Could this relationship actually be many-to-many?

For example, one company has many employees and each employee works for one company. That is Company 1:N Employee, with company_id in employees. One customer can have many addresses and each address belongs to one customer. That is Customer 1:N Address, with customer_id in addresses. One manager manages many employees and each employee has at most one direct manager. That is a recursive one-to-many relationship in the employees table, with manager_id referencing employee_id.

Business Requirement
        |
Identify Two Entities
        |
Ask: How many B per A?
        |
Ask: How many A per B?
        |
Relationship = 1:N
        |
Put Foreign Key on Many Side
        |
Choose NULL / NOT NULL
        |
Choose Delete Behavior
        |
Consider Indexing
        |
Test Referential Integrity

Implementation Pattern

The standard one-to-many implementation pattern is parent table with parent_id as primary key and child table with child_id as primary key plus parent_id as foreign key. The child foreign key can repeat because many child rows can belong to the same parent.

CREATE TABLE parent (
    parent_id INT PRIMARY KEY,
    parent_name VARCHAR(100)
);

CREATE TABLE child (
    child_id INT PRIMARY KEY,
    parent_id INT NOT NULL,
    child_name VARCHAR(100),
    FOREIGN KEY (parent_id)
        REFERENCES parent(parent_id)
);

This allows parent 1 to have child 1, child 2, child 3, and any number of additional children without changing the table design. That scalability of structure is one reason one-to-many relationships are a core relational design building block.

One-to-One vs One-to-Many

The main implementation question is whether the foreign-key value can repeat. If it can repeat, the relationship may be one-to-many. If it must be unique, the relationship may be one-to-one from the parent side.

One-to-Many vs Many-to-Many

Order and Product are a useful contrast. One order contains many products, but one product can also appear in many orders. That is not a simple one-to-many relationship between Order and Product. It is many-to-many and should be resolved through Order Item.

Complete E-Commerce Example

A common e-commerce design has Customer 1:N Order and Order 1:N Order Item. Customer is the parent of Order. Order is the parent of Order Item. Each order item belongs to one order, and each order belongs to one customer.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id)
        REFERENCES orders(order_id)
);

Customer 101
  - Order 5001
      - Item 1
      - Item 2
  - Order 5002
      - Item 3
      - Item 4
      - Item 5

This structure can grow naturally. A customer can have more orders by adding rows to orders. An order can have more items by adding rows to order_items. No new columns are needed when the number of children grows.

Interview-Ready Explanation

A short interview answer is: a one-to-many relationship means one parent record can be associated with many child records. In relational databases, it is implemented by placing the foreign key on the many side. For example, one customer can have many orders, so orders.customer_id references customers.customer_id.

A stronger answer is: in a 1:N relationship, the one side has the primary key and the many side stores that key as a foreign key. The foreign-key value is normally allowed to repeat because many child rows can reference the same parent. A UNIQUE constraint should not be placed on a normal one-to-many foreign key because it would change the relationship to maximum one child per parent. A complete design also considers optionality, referential integrity, delete behavior, indexing, history, and query behavior.

For a practical example, Customer 101 may have orders 5001, 5002, and 5003. The customer is stored once in customers, and each order row stores customer_id 101. A join can reconnect customer and order data when needed.

Key Takeaway

A one-to-many relationship means one parent can have many children. The fundamental SQL implementation is primary key on the one side and foreign key on the many side. The foreign-key value can repeat because multiple child rows may belong to the same parent.

ONE SIDE
Primary Key
     |
     | referenced by
     v
MANY SIDE
Foreign Key

The rule to remember is: in a one-to-many relationship, the foreign key goes on the many side. Do not put repeated child columns in the parent table. Do not store comma-separated child IDs. Do not add UNIQUE to the foreign key unless the business rule requires maximum one child. Model cardinality and optionality carefully, enforce referential integrity with foreign keys, choose delete behavior deliberately, and write joins with row multiplication in mind.