Database Design for Vibe Engineers

You can vibe-code a UI and throw it away tomorrow. You cannot do that with a database. Every feature you ship writes data into whatever shape your schema had that day, and reshaping data later — while the app is live, while users depend on it — is the most expensive kind of engineering there is.

This is the one layer where vibe engineers need to slow down and think like an architect. The good news: fifty years of database wisdom — Codd's relational model, Bill Kent's normal forms, Bill Karwin's antipattern catalog, the Postgres community's hard-won conventions — compresses into a few dozen rules you can learn in half an hour and apply forever.

How this document is organized. Every rule has a stable ID (T2, A3, C5…) and is written as one imperative sentence, then a short why and an example. Humans read it top to bottom. An AI coding assistant can be handed any section — or the cheat sheet at the end — as a rule set. When you tell your assistant "follow rule T3," this document is the reference.

0 · The One Rule#

One table = one kind of thing.

A table holds one entity — one category of thing your app cares about. Each row is one instance. Each column is one fact about it. The textbook version (Kroenke, Database Processing) is "one theme per table" — a table mixing two themes gets split.

Everything else in this guide answers two questions:

1. Is this a fact about an existing thing? → column on that table.
2. Is this a different kind of thing? → new table.

When unsure, use the pointing test: can I point at one of these? You can point at a student. You can point at a course. You can even point at an enrollment ("Maria's seat in Cohort 3"). If you can point at it and the app must track it, it's an entity — it gets a table.

1 · Building Blocks#

Three concepts carry the whole discipline.

B1 — Give every table a surrogate primary key. An id column that uniquely identifies each row and never changes. Never use a "meaningful" value like an email address as the primary key — emails change; ids never do. (Which type of id — see C1.)

B2 — Link tables with foreign keys, never by copying data. A foreign key is a column holding another table's id: enrollments.student_id points at a row in students. Data is referenced between tables, not duplicated.

B3 — Model every relationship as one of three shapes:

If you internalize nothing else, internalize the third row. Many-to-many requires a third table. Neither side can hold the relationship alone.

2 · When to Create a New Table#

Six signals. When you see one, split.

T1 — A thing can have many of something → child table. A student has one name (column). A student has many submitted assignments (table). The classic smell is numbered columns:

students: id, name, course1, course2, course3     ← wrong

The moment you're numbering columns, you need a child table with a foreign key back to the parent. A sharper version of the test: if you'd ever write a for-loop over column names, it should have been rows.

students:    id, name
enrollments: id, student_id, course_id, enrolled_at    ← right

T2 — Two things are many-to-many → join table. Students ↔ courses. Posts ↔ tags. Users ↔ roles. The join table can be tiny — sometimes just the two foreign keys — but it must exist. And once it exists, it becomes the natural home for facts about the relationship itself: enrolled_at, grade, completed_at. Those facts belong to neither the student nor the course — they belong to the enrollment.

T3 — The same fact would be copied into many rows → extract and reference. If every lesson row stores the instructor's name and bio, then a bio update means updating 200 rows — and missing one leaves your database disagreeing with itself. That's an update anomaly, the root disease all of this prevents. Fix: instructors gets its own table; lessons store instructor_id. The bio now lives in exactly one place.

T4 — The thing has its own lifecycle → own table. Certificates get issued, revoked, re-issued. Payments get created, retried, refunded. Anything born, changing state, and dying on its own schedule deserves its own table — even when it "belongs" to a parent.

T5 — Columns that only apply to some rows → hidden entity, split it out. If half the rows would be NULL across a group of columns, that group is a different entity in disguise. A courses table where cohort_start_date, cohort_size, and live_session_url are NULL for every self-paced course is telling you cohort is its own thing. Related heuristic (MSSQLTips): if adding the new columns makes you want to rename the table, you needed a new table.

T6 — Different security or access boundary → separate table. PII, billing details, and admin-only fields deserve their own table so row-level security and permissions can be drawn around them cleanly, instead of column-by-column exceptions on a shared table.

The whole decision, as a tree:

New data to store. Is it a fact about an existing entity?
├─ One value per row, same lifecycle, same audience?
│    └─ YES → column on that table (check K-rules)
├─ Can there be MANY per row?          → child table (T1)
├─ Does it link two entities N-to-N?   → join table (T2)
├─ Would the same fact repeat across rows? → extract + FK (T3)
├─ Own lifecycle / states of its own?  → own table (T4)
├─ NULL for most rows?                 → hidden entity, split (T5)
└─ Different security boundary?        → own table + RLS (T6)

3 · When to Keep Columns Together#

Splitting has a cost — every extra table is a join on every query that needs it.

K1 — One-to-one facts stay on the entity's table. Name, email, status, created_at, plan tier. These describe the row itself; splitting them out gains nothing.

K2 — Columns that live and die together belong together. If you'd never read, update, or delete one without the other, don't separate them. A course's title and description don't need separate homes.

K3 — Never split "for organization." Splitting students into students_basic and students_details — both one-to-one — doubles your joins for zero integrity benefit. Wide tables are fine. Duplicated facts are the problem, not column count. A 30-column table with no repeated data is healthier than five 6-column tables joined 1-to-1. (Legitimate exceptions exist — a hot narrow table split from a cold wide one for performance, or T6's security boundaries — but those are deliberate calls, not tidiness.)

4 · Normalization, Demystified#

You'll hear "normal forms" spoken of like dark arts. Here is the entire practical content of fifty years of theory:

"Every non-key field must provide a fact about the key, the whole key, and nothing but the key." — Bill Kent, A Simple Guide to Five Normal Forms (1983). Folk tradition appends: "…so help me Codd."

That one sentence is the first three normal forms:

N1 — Every fact lives in exactly one place. This is what normalization means, stripped of jargon. If you can imagine an update that requires changing the same fact in two places, the schema has a bug. Codd's own stated purpose: to free data from "undesirable insertion, update and deletion dependencies" — the three anomalies (can't record a fact until an unrelated one exists; change one copy and miss another; delete one fact and lose a different one).

N2 — Normalize to 3NF and stop. 3NF is the practical target — a database is colloquially called "normalized" when it gets there. BCNF/4NF/5NF handle edge cases most app schemas never hit. You do not need them; you need to stop putting commas in columns.

N3 — Denormalize later, deliberately, with evidence — never first. Jeff Atwood's formulation is the consensus: "Normalize until it hurts, denormalize until it works." Legitimate cases: a measured read bottleneck that indexing couldn't fix, cached aggregates (comment_count), reporting tables. Requirements when you do: one source of truth still exists, and the copy is maintained by one mechanism (trigger, job, queue) — not scattered app code. And keep Red Gate's counterpoint in your pocket: "If your database is slow, it isn't because it is over-normalized" — check your indexes before you blame your joins.

N4 — Snapshots are not denormalization. Copying the price onto order_items at purchase time is correct: the historical order must not change when the product's price does. That's not a redundant copy of a live fact — it's a different fact ("what they paid") that happens to share a number at write time. Beginners un-normalize the wrong things and "fix" this one; don't.

One honest caveat: the document-database world (MongoDB et al.) teaches the opposite instinct — "data that is accessed together should be stored together." That's real wisdom for that architecture. On Postgres, with joins that are fast and constraints that work, normalization-first is the right default; reach for embedded shapes only via M1's JSONB rule.

5 · The Antipattern Catalog#

Bill Karwin's SQL Antipatterns is the canon of schema mistakes — worth knowing by name, because AI assistants reproduce them constantly (see W5). Each row: the sin, why it fails, the fix.

6 · Mechanics: Keys, Names, Types, Constraints#

The conventions below are the settled Postgres/Supabase-era consensus (primary sources: the Postgres wiki's "Don't Do This" list, Simon Holywell's SQL Style Guide, Supabase's primary-key guidance).

Keys#

C1 — Use a surrogate primary key; enforce natural keys with UNIQUE. The id is meaningless on purpose (B1). If email must be unique, that's a UNIQUE constraint on email — not a primary key.

C2 — Default to bigint generated always as identity; use UUIDv7 when ids are exposed or generated by clients. Bigint is smallest and fastest for joins. Its one weakness: sequential ids leak information (row counts, enumerability) when exposed in URLs. Random UUIDs (v4) fix that but scatter inserts across the index — measurably slower at scale. UUIDv7 (time-ordered, core in Postgres 18) gives you both: use it for anything client-generated, distributed, or public. A common hybrid: bigint primary key internally, UUID public_id column for the outside world. And never serial — the identity syntax replaced it.

Naming#

C3 — snake_case, lowercase, always. Postgres folds unquoted identifiers to lowercase; fighting that means quoting identifiers forever.

C4 — Tables plural, columns singular, foreign keys <thing>_id. students, enrollments; name not student_name (don't repeat the table in the column). Join tables take the relationship's real name when it has one (enrollments beats students_courses), both names otherwise (course_tags). Honest footnote: singular-vs-plural is a genuine holy war (ISO standards say singular; Rails/Supabase practice says plural). Plural is the modern web default — but consistency matters more than the choice.

Constraints#

C5 — Put the rules in the database, not just the app. App code changes, has bugs, and is never the only client. Constraints are the last line of defense and living documentation: NOT NULL, UNIQUE on natural keys, CHECK (price >= 0), and real foreign keys everywhere (A7).

C6 — NOT NULL by default; a nullable column must answer "what does NULL mean here?" NULL means unknown, and it bites (NULL != x is not true; NOT IN with a NULL matches nothing). Declare NOT NULL unless absence is a legitimate state. A nullable boolean is a three-state smell — NOT NULL DEFAULT false. Many nullable columns clustered together → T5.

C7 — Choose ON DELETE behavior deliberately, and index every foreign key.

Postgres does not auto-index foreign key columns; unindexed FKs are the most common silent performance bug.

Types#

C8 — timestamptz always; text over varchar(n); numeric (or integer cents) for money — never float; never the money type. All four are straight from the Postgres wiki's Don't Do This. Floats binary-round your invoices. Bare timestamp gives wrong answers across timezones. varchar(50) is a future production error; if a length limit is a business rule, use text + CHECK.

C9 — Small fixed vocabularies: CHECK constraint; evolving vocabularies: lookup table; native enums sparingly. A status with five stable values → CHECK (status IN (...)) (easy to change later). Values that grow, need labels, ordering, or an admin UI → lookup table + FK. Native Postgres enums are fine for truly frozen sets but renaming/removing values is painful.

7 · Modern Patterns#

M1 — JSONB is a pressure valve, not a schema. Legitimate: payloads you don't control (webhook bodies, external API responses), user-defined settings blobs, rare unpredictable metadata. Not legitimate: queryable business facts. The test (via wirekat's JSONB guide): will it be filtered or joined often? does it need constraints? will code depend on its structure? — yes to any means it deserves a real column. Inside a JSON blob there are no types, no NOT NULL, no foreign keys; it's schema that only exists in your app's imagination. JSONB is also the sanctioned escape hatch that replaces EAV (A3).

M2 — Default to hard delete with an archive; soft-delete only what users expect to restore. The deleted_at pattern costs more than beginners think: every query forever must remember WHERE deleted_at IS NULL (forget once and "deleted" data resurfaces), foreign keys stop protecting you, and GDPR erasure eventually forces the hard delete anyway. Brandur Leach's write-up is the canonical case against — including the observation that real un-deletes almost never happen. His alternative: on delete, copy the row into one deleted_records table as JSONB (support can inspect it; live queries never see it). Use true soft delete for user-facing documents with a trash can; use hard delete for PII.

M3 — created_at and updated_at on every table; real history only where the business needs it. The two timestamps are near-free and universally expected (timestamptz NOT NULL DEFAULT now()). When you genuinely need "who changed what when" — financial records, compliance — add a trigger-fed audit table (table_name, row_id, action, old/new JSONB, actor, at). Don't build event sourcing because a blog post was exciting.

M4 — Multi-tenant apps: tenant_id column + row-level security, and index every column your policies reference. Schema-per-tenant and database-per-tenant exist for heavy isolation demands; for normal SaaS, a tenant_id on every tenant-owned table with RLS policies is the Supabase-era default — the database enforces isolation, so a forgotten WHERE clause can't leak a customer's data. The #1 RLS performance killer is a missing index on the policy column.

8 · Worked Example: The Academy#

The naive version an AI assistant might generate at 1 a.m.:

students: id, name, email, course1, course2, instructor_name,
          instructor_email, cert_issued, cert_url, amount_paid

Every alarm in this guide fires at once: numbered columns (T1/A2), instructor facts copied per student (T3), certificates and payments — things with their own lifecycles — crammed in as flags (T4).

Apply the rules — one table per thing you can point at:

students:     id, name, email, created_at
instructors:  id, name, email, bio
courses:      id, instructor_id, title, price, format
cohorts:      id, course_id, start_date, size
lessons:      id, course_id, title, position, video_url
enrollments:  id, student_id, cohort_id, enrolled_at, completed_at
submissions:  id, enrollment_id, lesson_id, submitted_at, grade
certificates: id, enrollment_id, issued_at, revoked_at, url
payments:     id, enrollment_id, amount, status, created_at

Nine tables, each earning its existence: enrollments is the T2 join table — and completed_at lives there, because completion is a fact about the enrollment, not the student or the course. instructors exists so a bio update touches one row (T3). certificates and payments have their own lifecycles (T4). lessons hang off courses with a plain FK — one-to-many needs no join table (B3).

And here is one table with every mechanical rule applied at once:

create table enrollments (
  id            bigint generated always as identity primary key,   -- C2
  student_id    bigint not null references students on delete cascade,  -- C5, C7
  cohort_id     bigint not null references cohorts  on delete restrict, -- C7
  enrolled_at   timestamptz not null default now(),                 -- C8, M3
  completed_at  timestamptz,                                        -- NULL = "not yet" (C6: it means something)
  grade         numeric(5,2) check (grade between 0 and 100),       -- C5, C8
  unique (student_id, cohort_id)                                    -- C1: the natural key, enforced
);
create index on enrollments (student_id);   -- C7: index your FKs
create index on enrollments (cohort_id);

9 · Working With an AI Assistant#

Design principles are half the story. The other half is workflow — how to build with an AI assistant without letting it erode the schema. This isn't paranoia; it's measured. Timescale's pg-aiguide benchmarks found unguided agents produce schemas with roughly 4× fewer constraints than guided ones; a late-2025 security assessment found 69 vulnerabilities across 15 vibe-coded apps; and the single most common hole in AI-built Supabase apps is missing row-level security. The failure modes are almost exactly Karwin's catalog — missing foreign keys and FK indexes, JSON blobs standing in for design, near-duplicate tables — which is precisely why the rule IDs above exist: they give you and your assistant a shared vocabulary for review.

W1 — Make the AI propose the schema before it writes any code. "List the tables, columns, and relationships as plain text. Don't write the migration until I approve." Schema is the one layer where you always review the plan.

W2 — Never let the AI invent a table mid-task. New storage is a design decision: stop, run the T-signals, decide deliberately. The documented failure mode is schema drift: over a long session the assistant loses the thread and creates parallel near-duplicates — users, user_profiles, app_users — instead of extending what exists. Standing instruction for your assistant: "before creating any table, list the existing tables and state why none of them is the right home."

W3 — Don't store what you can compute. No total_spent column when you can sum payments. Stored derived values go stale the moment one code path forgets to update them — the update anomaly wearing a disguise. Cache later, with evidence (N3).

W4 — Every schema change is a migration file, reviewed like code. Never hand-edit a live schema. History should be reconstructable from the repo.

W5 — Review every AI-generated schema against the catalog. The five-minute check, in order: real foreign keys everywhere (A7)? Indexes on them (C7)? Any comma-lists, numbered columns, JSON-as-schema (A1, A2, M1)? Any table failing the pointing test (§0)? NOT NULL where it should be (C6)?

W6 — If clients talk to the database directly (Supabase, Firebase), row-level security is part of the schema design. Who can read which rows is decided when you design the table, not after the leak (M4, T6). The Supabase-specific traps, because they recur in audit after audit: RLS is off by default on new tables — enable it before the table is exposed, even with a permissive policy; filtering by user_id in app code does nothing if the table itself is world-readable; never authorize off user_metadata (users can edit it); and test policies through the client SDK, not the SQL editor — the editor bypasses RLS, so everything looks fine there.

10 · Cheat Sheet#

The condensed rule set — designed to be pasted whole into an assistant's context.

DATABASE DESIGN RULES (compressed)

CORE
- One table = one kind of thing. Rows are instances; columns are facts.
- Every fact lives in exactly one place. (This is normalization: N1)
- Kent's law: every column is a fact about the key, the whole key,
  and nothing but the key. Target 3NF; stop there. (N2)

SPLIT into a new table when:              (T-rules)
- T1: an entity can have MANY of something (never numbered columns)
- T2: many-to-many → join table, always; relationship facts live on it
- T3: the same fact would repeat across rows → extract + reference
- T4: the thing has its own lifecycle (payments, certificates)
- T5: a column group is NULL for most rows → hidden entity
- T6: different security/RLS boundary (PII, billing)

KEEP together when:                       (K-rules)
- K1: one-to-one facts about the entity
- K2: columns that live and die together
- K3: never split for tidiness — wide is fine, duplicated is not

NEVER (antipatterns):                     (A-rules)
- A1: comma-separated lists in a column
- A2: numbered columns (photo_1, photo_2)
- A3: entity-attribute-value tables
- A4: polymorphic FKs (x_type + x_id)
- A5: per-year/per-customer clone tables
- A6: god tables (50+ nullable columns)
- A7: FK columns without FK constraints

MECHANICS:                                (C-rules)
- C1: surrogate PK + UNIQUE on natural keys
- C2: bigint identity by default; UUIDv7 if exposed/client-generated;
      never serial, avoid UUIDv4 PKs
- C3/C4: snake_case; tables plural; columns singular; FKs <thing>_id
- C5: constraints in the DB: NOT NULL, UNIQUE, CHECK, real FKs
- C6: NOT NULL by default; every nullable column must mean something
- C7: choose ON DELETE deliberately (cascade=components,
      restrict=independent value, set null=optional); index every FK
- C8: timestamptz; text; numeric or integer cents for money; no float
- C9: CHECK for small fixed sets; lookup table for evolving sets

MODERN:                                   (M-rules)
- M1: JSONB only for data you don't control or won't query;
      queryable business facts get real columns
- M2: hard delete + archive table by default; soft delete only
      for user-restorable documents
- M3: created_at/updated_at everywhere; audit tables only where
      the business demands history
- M4: multi-tenant = tenant_id + RLS; index policy columns

WORKFLOW (AI-assisted):                   (W-rules)
- W1: schema proposed and approved before code
- W2: no new tables invented mid-task
- W3: never store what you can compute (N3: denormalize only
      with evidence; N4: snapshots like order prices are correct)
- W4: migrations only, reviewed like code
- W5: check AI schemas for A1-A7 + missing FK indexes
- W6: RLS designed with the table, enabled before exposure;
      never authorize off user_metadata; test via client SDK
      (SQL editor bypasses RLS)

Sources & Further Reading#

The canon this guide compresses, in reading order:

• Bill Kent — A Simple Guide to Five Normal Forms (1983) — the whole theory in nine pages
• Bill Karwin — SQL Antipatterns (Pragmatic Bookshelf) — the catalog of §5, with the fixes
• Postgres wiki: Don't Do This — the types-and-features blacklist behind C8
• Simon Holywell — SQL Style Guide — naming conventions
• Jeff Atwood — Maybe Normalizing Isn't Normal — the pragmatist case; pair with Red Gate's "The Myth of Over-Normalization" for the rebuttal
• Supabase — Choosing a Postgres Primary Key — the bigint/UUID decision in depth
• Brandur Leach — Soft Deletion Probably Isn't Worth It — the case behind M2