One-to-Many Relationship

One-to-many is the most common relationship type.

Examples: customer-to-orders, author-to-posts, category-to-products.

Core Concepts

erDiagram
  CUSTOMER ||--o{ ORDER : places

  CUSTOMER {
    integer customer_id PK
    text name
  }

  ORDER {
    integer order_id PK
    integer customer_id FK
    real total
  }

Table role	Key design
Parent (customers)	Primary key (customer_id)
Child (orders)	Foreign key (customer_id)

Code Examples

-- Parent table.
CREATE TABLE customers_1m (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

-- Child table: many orders can reference one customer.
CREATE TABLE orders_1m (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers_1m(customer_id)
);

Expected output
One-to-many schema created successfully.

-- Sample data and relationship query.
INSERT INTO customers_1m (customer_id, name)
VALUES (1, 'Ada');

INSERT INTO orders_1m (order_id, customer_id, total)
VALUES (201, 1, 25.00), (202, 1, 45.50);

SELECT c.name, o.order_id, o.total
FROM customers_1m AS c
INNER JOIN orders_1m AS o
  ON c.customer_id = o.customer_id
ORDER BY o.order_id;

name	order_id	total
Ada	201	25.00
Ada	202	45.50

SQLite-Specific Nuances

SQLite Nuance

The child foreign key column in one-to-many should typically be indexed as data grows to keep joins and lookups efficient.

Common Pitfalls / Best Practices

Pitfall

Storing parent data redundantly in the child table (for example, repeating customer name on every order row).

Best Practice

Store parent identity in the child via foreign key and pull parent attributes via joins.

Quick Challenge

Model authors and posts as one-to-many (one author, many posts).

View Solution

CREATE TABLE authors (
  author_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL
);

CREATE TABLE posts (
  post_id    INTEGER PRIMARY KEY,
  author_id  INTEGER NOT NULL,
  title      TEXT NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Expected output
One author can reference many post rows via posts.author_id.