Skip to content

Start Here — Database First Principles

Level: Beginner · A worked model to reuse throughout the guide

A database is more than a place to store rows. It maintains facts, evaluates queries and coordinates changes under concurrency and failure. Before selecting a product, answer: what must remain true, how will we access it, and which failures must it survive?

1. Separate Five Independent Decisions

Five layers of database design: facts, access patterns, correctness, physical layout, and operations

Edit this diagram in draw.io.

Layer Question Example
Logical model What facts and relationships exist? Customers place orders containing products
Access patterns Which questions must be fast? Latest 20 orders for one customer
Correctness What must never happen? An order cannot reference a nonexistent customer
Physical design How is work reduced or distributed? Composite index; later, tenant-based sharding
Operations How do we detect and recover from failure? Lag alerts, retained backups, tested restore

Do not confuse a logical model with a storage engine or a consistency model. A relational database can store JSON, a document database can run transactions, and a SQL database can be distributed.

graph LR
    Facts[Define facts and invariants] --> Queries[List access patterns]
    Queries --> Design[Choose the simplest design]
    Design --> Measure[Test correctness and performance]
    Measure --> Refine[Refine from evidence]

2. A Small Relational Model

Use a disposable local PostgreSQL database for the following lab. The statements create new tables and add synthetic data; they are not intended for an existing production schema.

CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    current_price numeric(12, 2) NOT NULL CHECK (current_price >= 0)
);

CREATE TABLE orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers,
    created_at timestamptz NOT NULL DEFAULT current_timestamp,
    status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))
);

CREATE TABLE order_items (
    order_id bigint NOT NULL REFERENCES orders,
    product_id bigint NOT NULL REFERENCES products,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price_at_purchase numeric(12, 2) NOT NULL CHECK (unit_price_at_purchase >= 0),
    PRIMARY KEY (order_id, product_id)
);
  • A primary key uniquely identifies a row and cannot be null.
  • A candidate key is a minimal set of attributes that uniquely identifies a row. Choose one as the primary key and preserve other required uniqueness rules.
  • A foreign key enforces a reference; it does not automatically make every join fast.
  • A check constraint protects a row-level rule. Cross-row business rules often need a transaction and appropriate concurrency control.
  • NOT NULL matters: SQL has three-valued logic, and a check expression evaluating to unknown does not reject a null by itself.
  • Use fixed-precision decimal values or a clearly defined integer-minor-unit model for money, not binary floating point. Real financial systems also need currency and rounding rules.

The composite key allows one line per product per order. If repeated product lines are a requirement, introduce a line identifier and revisit the uniqueness rule rather than forcing the business into the example.

3. Follow One Query Through the System

SELECT o.order_id, SUM(i.quantity * i.unit_price_at_purchase) AS total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.customer_id = 1
GROUP BY o.order_id
ORDER BY o.order_id;

A join matches related facts; it does not inherently mean a full scan. The optimizer chooses scans and join algorithms from statistics and estimated cost. An inner join excludes orders with no items. Use a left join plus deliberate null handling if empty orders must appear.

Common mistake Better reasoning
SELECT * everywhere Read only needed columns, especially for wide/columnar tables
column = NULL Use IS NULL; understand unknown in filters
Joining two one-to-many children before summing Pre-aggregate at the intended grain to avoid multiplying rows
Large OFFSET pagination Consider keyset pagination with a stable, unique tie-breaker
Assuming SQL textual clause order is execution order Inspect the physical plan; the optimizer can reorder safe operations

Continue with index design and EXPLAIN, then normalization to understand why each fact has its own home.

4. Make Atomic Changes

BEGIN;
INSERT INTO customers (email) VALUES ('learner@example.test');
INSERT INTO products (name, current_price) VALUES ('Database workbook', 25.00);
WITH new_order AS (
    INSERT INTO orders (customer_id, status)
    SELECT customer_id, 'pending'
    FROM customers
    WHERE email = 'learner@example.test'
    RETURNING order_id
)
INSERT INTO order_items (order_id, product_id, quantity, unit_price_at_purchase)
SELECT n.order_id, p.product_id, 2, p.current_price
FROM new_order AS n
CROSS JOIN products AS p
WHERE p.name = 'Database workbook';
COMMIT;

Run the seed transaction once in the fresh lab schema. It creates an order and its line together. Query the order total: it should be 50.00. This example does not reserve inventory or charge an external payment API. Those are additional invariants and failure boundaries, not properties SQL invents for you.

Try inserting a negative quantity in a separate transaction: the check constraint rejects it. If a statement fails inside an explicit PostgreSQL transaction, issue ROLLBACK before continuing. Then read ACID and isolation to distinguish all-or-nothing from safe concurrent decisions.

5. Scale Only the Bottleneck You Have

Observation Investigate first Escalate when evidence supports it
Slow selective query Plan, index, statistics, lock waits Read replica for offload with a freshness contract
Repeated expensive reads Query shape and precomputation Cache or materialized view with invalidation/refresh rules
Write contention on one row Transaction duration and invariant design Split the logical hotspot if business rules allow
One server cannot hold or process the dataset Capacity and measured workload Sharding with routing, migration and cross-shard semantics
Need recovery from accidental deletion Backup retention and restore tests Replication alone cannot solve this

Latency percentiles matter: averages can hide severe p99 delays. Throughput, concurrent requests and latency are related but not interchangeable. Record workload shape, dataset size, hardware, cache state and durability settings with every benchmark.

Check Your Understanding

#

Question 1

Why is the purchase price stored on the order line when the product already has a price?

#

Question 2

Which constraints prevent two customers from using the same non-null email in this schema?

#

Question 3

Will sharding automatically fix ten thousand updates per second to the same logical counter?

Next Steps

Follow SQL vs NoSQL, ACID, normalization and indexing. Before distributed transactions, revisit isolation, WAL and replication. Keep the reference and freshness guide open when comparing product-specific claims.