---
topic: system-design
author: Crashtech Editorial
published: Nov 14, 2026 · read: 4 min
---

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.

– –

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.

RelationshipExampleModel
One to manyA customer has many ordersForeign key on the many side (orders.customer_id)
Many to manyStudents and coursesJunction table (enrollments) with two foreign keys
One to oneA user and their profile settingsForeign 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.

Surrogate key plus natural unique stable identity
The id never changes. email and sku stay unique through constraints, so duplicates are impossible even though they are not the key.
Money as integers no rounding bugs
Cents in a BIGINT. Floating point and money do not mix.
Snapshot the price history is a fact
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.
Constraints state the rules enforced for everyone
Statuses, non-negative amounts, positive quantities and valid references are checked by the database, not hoped for.

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), or SET 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.
Index your foreign keys

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.

Advertisement

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 shapeIndex
WHERE customer_id = ?Index on the foreign key
WHERE status = ? ORDER BY created_at DESCComposite (status, created_at)
Joins on order_items.product_idIndex on product_id (the PK covers order_id first)
Case-insensitive lookupExpression 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.

Advertisement

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.

Sources & further reading

/* Comments */