Relational Model

The relational model organizes data into tables and connects them through keys.

If your model is clear, queries stay simple and data stays consistent.

Core Concepts

erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--o{ ORDER_ITEM : contains
  PRODUCT ||--o{ ORDER_ITEM : referenced_by

  CUSTOMER {
    integer customer_id PK
    text name
    text email
  }

  ORDER {
    integer order_id PK
    integer customer_id FK
    text order_date
  }

  PRODUCT {
    integer product_id PK
    text name
    real price
  }

  ORDER_ITEM {
    integer order_id FK
    integer product_id FK
    integer qty
  }

Building block	Purpose
Entity table	Stores one type of thing (customers, orders)
Primary key	Unique row identity
Foreign key	Connects rows across tables
Relationship cardinality	One-to-one, one-to-many, many-to-many

Code Examples

-- Minimal relational model setup.
CREATE TABLE customers_rel (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE orders_rel (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  order_date   TEXT NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers_rel(customer_id)
);

Expected output
Two related tables created successfully.

-- Show relationship path with a join.
SELECT o.order_id, c.name, o.order_date
FROM orders_rel AS o
INNER JOIN customers_rel AS c
  ON o.customer_id = c.customer_id;

order_id	name	order_date
Rows appear after inserting sample data

SQLite-Specific Nuances

SQLite Nuance

In SQLite, relationships are modeled with normal SQL tables and foreign key clauses, but enforcement depends on connection settings.

You will enable and verify this in the foreign keys lesson.

Common Pitfalls / Best Practices

Pitfall

Designing relationships from query convenience instead of real business rules.

That often creates duplicate data and confusing joins.

Best Practice

Model entities first, then define keys, then validate cardinality (one-to-one, one-to-many, many-to-many).

Quick Challenge

Identify the likely relationship type between customers and orders in a shopping app.

View Solution

Usually one-to-many: one customer can place many orders.