Design Patterns

By the end of this lesson you'll be able to design schemas that hold up in real, large systems — choosing transactional vs analytical layouts, the right key strategy, lookup and junction tables, hierarchies, soft deletes, audit trails, and knowing exactly when to break the rules of normalisation. This is the difference between a schema that survives and one you rebuild at 2am.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

A schema is the foundation and frame of a building. You can repaint walls (queries) any time, but moving a load-bearing wall (the table design) after people have moved in is enormously expensive. These patterns are the structural-engineering rules that stop the building falling down as it grows from a cottage into a tower.

There's no single sample table this time — each pattern brings its own small schema. Read the comments as the lesson; they state the why and the trade-off for every design choice.

Before any table, ask one question: is this database serving an app or answering analytics ? They pull in opposite directions.

OLTP (Online Transaction Processing) is your live app: thousands of tiny reads and writes a second, each touching one or two rows. You normalise — store each fact once — so an update is consistent and cheap.

OLAP (Online Analytical Processing) is the dashboard: a few enormous queries that scan history and aggregate. You denormalise into a star schema so analysts join less and scan faster.

Web apps, banking, checkout. Fast single-row operations. Normalised, write-heavy.

Warehouses, BI. Huge aggregations over millions of rows. Denormalised, read-heavy.

A primary key uniquely identifies a row. A natural key uses real-world data (an email, an ISBN). A surrogate key is a meaningless auto-number ( BIGSERIAL , IDENTITY , or a UUID ) that exists only to be the identifier.

The problem with natural keys: real-world data changes . If email is your key and every order references it, a customer changing their email forces you to rewrite every foreign key. Surrogate keys never change and never get reused, so foreign keys stay stable forever.

When a column can only hold a small fixed set of values — statuses, roles, countries — don't store free text. Put the allowed values in a tiny lookup table and reference it with a foreign key. Now a typo like 'Shiped' is rejected by the database, not silently saved.

A one-to-many relationship (one customer, many orders) fits with a foreign key on the "many" side. But many-to-many — students and courses, posts and tags — won't fit in either table. The fix is a third junction table (also called a bridge or linking table) that stores one row per pairing.

Give the junction a composite primary key of both foreign keys: that guarantees a student can't be enrolled in the same course twice.

To read across the relationship, you join through the junction in the middle — connect the left side to the link, then the link to the right side.

Fill in the one blank to finish the join, then aggregate. The expected result is in the comments so you can check yourself.

Categories with subcategories, employees with managers, threaded comments — these are trees , and a table can point at itself to model them. There are two classic patterns with opposite trade-offs.

The adjacency list stores each row's parent_id . Writes are trivial, but reading a whole subtree needs a recursive query.

The closure table stores every ancestor→descendant pair, so reading a subtree is a plain join — but every move means maintaining many path rows.

Real systems rarely truly delete data. A soft delete flags a row as gone ( is_deleted and/or deleted_at ) instead of removing it — so you keep history, can undo mistakes, and never lose referenced rows. The catch: every normal query must add WHERE is_deleted = FALSE .

Add created_at and updated_at to almost every table for free bookkeeping. For anything regulated or sensitive, also keep an audit / history table that records a copy of each change with who and when — so you can reconstruct any row's past.

Fill in the soft-delete filter, then read the key-strategy reasoning in the comments and confirm you'd make the same call.

EAV (Entity-Attribute-Value) stores one row per attribute, so any entity can carry any fields without schema changes. It looks like ultimate flexibility — and it's one of the most common ways to wreck a database.

Because every value is a string in one column, you lose data types, NOT NULL , foreign keys, and simple filters like WHERE ram_gb 16 . A single logical record sprays across many rows, so even reading one item needs an ugly pivot.

Normalisation — storing each fact exactly once — is the default, because it keeps writes consistent. But on a proven read hot-path, joining the same tables a million times a day can dominate your latency.

Deliberate denormalisation copies a value (say, the customer's name onto the orders table) to skip a join. It's a conscious trade: faster reads in exchange for having to keep the copy in sync on writes. Do it only after measuring, and document why — accidental duplication is a bug, intentional duplication is an optimisation.

Q: Should I use auto-increment integers or UUIDs for surrogate keys?

Both are surrogate keys. Auto-increment ( BIGSERIAL ) is compact and index-friendly; UUID is globally unique without a central counter, which helps with distributed systems and hiding row counts. Pick UUIDs when IDs are generated outside one database or must not be guessable.

For a while, yes — a normalised OLTP database can also run reports. As volume grows, the two workloads fight over the same machine, so teams replicate the data into a separate warehouse (the star schema) for analytics and leave the app database lean.

No — accidental duplication is a bug, but deliberate denormalisation is a recognised optimisation. The test is whether it's intentional, measured, and documented, and whether you have a plan to keep the copies in sync.

Q: Adjacency list or closure table for my tree?

Default to the adjacency list — it's simplest, and recursive CTEs handle most reads fine. Switch to a closure table only when you query whole subtrees so often that the recursion becomes the bottleneck.

Put the patterns together — a brief, an outline, and the expected shape in the comments. Write the CREATE TABLE statements, then paste them into a playground to confirm they run.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary