Relational Schema Design That Survives Contact With Production
Keys, relationships, constraints and indexes: the schema decisions that decide integrity and speed, with worked SQL for one-to-many and many-to-many.
On this page
Using a relational database does not make your data model good. The same engine hosts schemas that guarantee integrity for decades and schemas that quietly collect duplicates. The difference is a handful of habits applied while the tables are still empty.
Start from entities and relationships
List the nouns your product cares about, then say how many of each relate to how many of another.
| Relationship | Example | Model |
|---|---|---|
| One to many | A customer has many orders | Foreign key on the many side (orders.customer_id) |
| Many to many | Students and courses | Junction table (enrollments) with two foreign keys |
| One to one | A user and their profile settings | Foreign key with a UNIQUE constraint, or the same table |
A worked schema
CREATE TABLE customers (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
status TEXT NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price_cents BIGINT NOT NULL CHECK (price_cents >= 0)
);
CREATE TABLE order_items ( -- junction: orders <-> products
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price_cents BIGINT NOT NULL, -- price at time of sale, not a live lookup
PRIMARY KEY (order_id, product_id)
);
Several quiet decisions are in there.
id never changes. email and sku stay unique through constraints, so duplicates are impossible even though they are not the key.unit_price_cents on the line item records what was charged. If you join to the live product price, last year’s invoice changes when you edit the catalogue.Keys
- Primary key: unique, never null, never reused, rarely changed. Short and sequential keys also keep indexes compact.
- Foreign key: a promise that the referenced row exists. Decide what happens on delete:
RESTRICT(refuse),CASCADE(delete children), orSET NULL. Pick it consciously, because the default is rarely what the business means. - Composite key: natural for junction tables, where the pair is the identity.
Primary keys get an index automatically. Foreign key columns in PostgreSQL do not. Without an index on orders.customer_id, “all orders for a customer” scans the whole table, and deleting a customer scans it again to check for children.
Normalization, practically
Normalizing means each fact lives in one place, so an update happens once and cannot disagree with itself.
- Keep atomic values in columns. A comma-separated list in a cell is a missing table.
- Every non-key column should depend on the key, the whole key, and nothing but the key.
- Repeat nothing you would have to update in two places, such as a customer’s name on every order row.
Normalize first because it is the version that cannot go inconsistent. Denormalize later, for a measured read problem, and keep the source of truth so you can rebuild the copy.
Indexing for the queries you actually run
| Query shape | Index |
|---|---|
WHERE customer_id = ? | Index on the foreign key |
WHERE status = ? ORDER BY created_at DESC | Composite (status, created_at) |
Joins on order_items.product_id | Index on product_id (the PK covers order_id first) |
| Case-insensitive lookup | Expression index, or a normalized column |
Look at the plan (EXPLAIN ANALYZE) before and after adding an index. Every index you add slows writes and uses disk, so each should answer a query you can name.
Habits that prevent most schema regrets
Do
Use NOT NULL by default and allow NULL only when “unknown” is a real state. Store timestamps with a time zone. Name constraints. Make schema changes through versioned migrations, never by hand in production.
Don't
Store JSON blobs for data you filter and join on, use free-text columns for statuses, or skip foreign keys “for performance”. Missing constraints do not make writes faster in any way you will notice, and they make bad data permanent.
Takeaway
A relational schema is a set of facts and the rules those facts must obey. Model entities and relationships explicitly, put every rule you can into constraints, index the foreign keys and filters you use, and normalize by default. Do that on day one and the database spends the next ten years protecting you instead of surprising you.
Frequently asked questions
Should every table have a surrogate primary key?
Usually yes: a compact, stable, meaningless key avoids cascading updates when business data changes. Keep a unique constraint on the natural identifier too, such as an email or SKU, so the database still prevents duplicates the surrogate key would let through.
How do I model a many-to-many relationship?
With a junction table holding the two foreign keys, usually as a composite primary key, plus any attributes of the relationship itself. Students and courses become a third table, enrollments, that can also store the enrolment date and grade.
Why use database constraints when the application validates input?
Applications have bugs, multiple services write the same tables, and people run manual fixes. Constraints are enforced for every writer, every time. Treat application validation as the friendly layer and constraints as the guarantee that bad data cannot exist.
When is it acceptable to denormalize?
When measurements show a read path is too slow and an index or query change cannot fix it. Denormalize deliberately, record why, and keep the normalized source of truth so the copy can be rebuilt. Doing it first, without evidence, just adds update anomalies.

/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.