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¶
| 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 NULLmatters: 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.