Schema Design Fundamentals

Before you write a single line of code — or prompt an AI agent to write it — you need to decide what data your system stores and how that data is structured. This is schema design: the practice of defining entities, their attributes, and the relationships between them before writing any code.

Getting the schema right from the start pays off at every stage. A well-designed schema is easy for AI agents to extend correctly, easy for developers to query efficiently, and resistant to the silent data corruption bugs that compound over time. A poorly designed schema does the opposite — and fixing it later means writing risky migrations, updating every query, and hoping nothing breaks in production.

This tutorial covers the foundational vocabulary and decisions every developer needs: what goes into a schema, how relationships are modeled, the constraints that enforce data correctness, and the specific mistakes AI agents consistently make when generating schemas.

From Spreadsheet to Schema#

A helpful mental model: think of a table as a spreadsheet. Each column is a named field with a specific type (name, email, timestamp). Each row is one record. The critical difference is that a relational database enforces the structure — the data types, the constraints, the connections between tables. That enforcement is what makes data trustworthy at scale. If an application tries to insert a row that violates any rule — a missing required field, a duplicate unique value, or a reference to a non-existent parent row — the database rejects it outright.

A schema is the full definition of all your tables, columns, types, constraints, and relationships. It is the contract between your application and your database.

Entities, Attributes, and Tables#

The starting point for any schema is identifying your entities — the meaningful "things" your system tracks. Entities become tables. Their properties become columns.

• An entity is a distinct object or concept: User, Order, Product, Post
• An attribute is a property of that entity: a User has a name, email, and created_at
• A relationship connects entities: a User places Orders

This analysis — identifying entities, attributes, and relationships — produces an Entity-Relationship Diagram (ERD), the visual blueprint of your schema. An ERD shows every table, its columns, and how the tables connect to each other. It is the most valuable artifact you can give an AI agent before asking it to generate any schema code, because it gives the agent a concrete specification to implement rather than a vague domain to interpret.

A note on table naming: Use plural nouns for table names (users, not user; orders, not order) — this is the dominant SQL convention and the one followed throughout this tutorial. Your ORM entity classes and objects can still use singular names (User, Order); the table name in the database is what should be plural. Whatever convention your team chooses, document it in your CLAUDE.md or project rules file so every AI session follows the same convention automatically.

Primary Keys, Foreign Keys, and Constraints#

Constraints are the database's enforcement mechanism. They are the rules that make data trustworthy — rules that the database guarantees at every write, so your application code does not have to duplicate them. Even if a bug in your application tries to insert invalid data, a constraint will stop it at the database level.

Primary Keys#

Every table should have a primary key (PK) — a column whose value uniquely identifies each row. Primary keys cannot be NULL and cannot contain duplicates. Every row is guaranteed to be reachable by its primary key.

Use a surrogate key, not a natural key.

A natural key is something that exists in the real world: an email address, a phone number, or a username. The temptation is to use it as the primary key because it is already unique. The problem: it changes. A user updates their email, and now every table that stored that email as a foreign key must also be updated. Cascading updates across multiple tables are error-prone, slow, and can corrupt historical records — for example, an order that was placed under the old email may point to nothing after the update.

A surrogate key is synthetic — an auto-incremented integer (SERIAL / BIGSERIAL in Postgres) or a UUID (gen_random_uuid() in Postgres 13+). It carries no business meaning, never changes, and never needs to be updated. UUIDs are especially useful in distributed systems where records may be created across multiple services before being written to a central database, since UUIDs can be generated independently on any client without coordination.

-- Good: surrogate key
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,  -- unique, but not the PK; can be indexed separately
    username TEXT NOT NULL
);

-- Risky: natural key as PK
CREATE TABLE users (
    email    TEXT PRIMARY KEY,  -- what happens when a user changes their email?
    username TEXT NOT NULL
);

Foreign Keys#

A foreign key (FK) is a column in a child table that references the primary key of a parent table. It enforces referential integrity: the database will reject any attempt to insert a child row pointing to a non-existent parent.

CREATE TABLE posts (
    id        SERIAL PRIMARY KEY,
    author_id INTEGER NOT NULL,
    title     TEXT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

The ON DELETE directive tells the database what to do when the referenced parent row is deleted:

Always add an index on your foreign key columns. Without an index, every JOIN on that column forces the database to scan the entire table — checking every row to find matching records. At scale with millions of rows, this becomes catastrophically slow. This is one of the most consistent mistakes in AI-generated schemas: the FK constraint is declared, but the index is missing.

-- Always add this index after declaring a foreign key:
CREATE INDEX idx_posts_author_id ON posts(author_id);

Column Constraints#

AI agents routinely omit constraints. An AI generates syntactically valid SQL without NOT NULL or UNIQUE because the model's goal is to complete the prompt — not to enforce your data integrity rules. The missing constraints are silent failures: the schema runs, tests pass, and then one day a bug inserts a NULL into an email column, and your authentication query matches every user with a NULL email instead of nobody. Review every AI-generated schema for missing constraints before running any migration.

The Three Relationship Types#

Every relationship between two entities falls into one of three categories. Getting the category right is critical — it determines where the foreign key lives, whether you need a junction table, and what constraints are needed to prevent duplicate or orphaned data.

One-to-One (1:1) — Selective Splits#

One parent row relates to exactly one child row, and each child belongs to exactly one parent. The foreign key can live in either table, but it must have a UNIQUE constraint — without it, the database allows multiple child rows to reference the same parent, silently degrading the relationship into 1:N.

Use 1:1 relationships sparingly. Common reasons to split a table this way include:

• Security isolation: storing sensitive fields (payment credentials, government IDs) in a separate table with stricter access controls
• Performance: keeping large optional fields (profile bios, avatar metadata, settings blobs) out of the main table so that queries that don't need them stay fast

CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL
);

-- 1:1: each user has at most one extended profile
CREATE TABLE user_profiles (
    id         SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL UNIQUE,   -- UNIQUE enforces the 1:1 limit
    bio        TEXT,
    avatar_url TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

If you always query the profile together with the user, ask yourself whether the JOIN is worth the split. A 1:1 relationship only pays off when there is a concrete access pattern or security reason to keep the data separate.

One-to-Many (1:N) — The Workhorse#

One parent row relates to many child rows; each child belongs to exactly one parent. The foreign key always lives in the child (many) table.

-- One customer places many orders
CREATE TABLE customers (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id            SERIAL PRIMARY KEY,
    customer_id   INTEGER NOT NULL,       -- FK lives in the "many" side
    total_amount  NUMERIC(10, 2) NOT NULL,
    created_at    TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Many-to-Many (M:N) — The Junction Table#

When both sides of a relationship can have many connections, a single foreign key is not sufficient — there is no single table to put it in without duplicating data. You need a junction table (also called a join table or bridge table) — a dedicated table that holds a foreign key to each side and whose combined keys form a composite primary key.

CREATE TABLE post_tags (
    post_id  INTEGER NOT NULL,
    tag_id   INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),          -- composite PK prevents duplicate tag assignments
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);

The junction table's composite primary key (post_id, tag_id) serves two purposes: it uniquely identifies each row, and it enforces the rule that a post cannot be tagged with the same tag twice. Junction tables can also carry additional columns that describe the relationship itself — an enrollment date, a user's role within a group, or an assigned-at timestamp — making them richer than a simple link between two tables.

Cardinality Notation: Reading ERDs#

When you read or generate ERDs, you will encounter Crow's Foot notation — the standard visual language for expressing how many rows can participate in a relationship. Each end of a relationship line has two symbols: one showing the minimum (can zero rows participate?) and one showing the maximum (is there a cap of one, or can there be many?). Learning to read it lets you verify AI-generated ERDs in seconds.

Always read each relationship line from both directions to fully understand the constraint:

• CUSTOMER ||--o{ ORDER → "A customer places zero or many orders. Each order belongs to exactly one customer."
• ORDER ||--|{ LINE_ITEM → "An order contains one or many line items. Each line item belongs to exactly one order."

The inner symbol (the one closest to the entity box) represents the minimum cardinality — a circle means zero (the relationship is optional), a vertical bar means one (mandatory, at least one must exist). The outer symbol represents the maximum cardinality — a single vertical bar means at most one, a crow's foot (the three-pronged mark) means many.

JSONB vs. Normalized Columns#

Modern databases like PostgreSQL let you store JSON objects directly in a column using the JSONB type. This is genuinely useful in specific situations, but it is frequently misused as a shortcut to avoid thinking through the data model — and the performance consequences at scale can be severe.

The performance trap: PostgreSQL's query planner cannot collect statistics on fields inside a JSONB column the way it can for regular columns. When you filter by a JSONB field (e.g., WHERE data->>'status' = 'active'), the planner falls back to a hardcoded 0.1% selectivity estimate — meaning it assumes the filter matches almost nobody, regardless of the actual data distribution. This causes the planner to choose catastrophically bad query plans. Heap Engineering documented a real case where a query ran in 584 seconds instead of 300ms — a 2,000× slowdown — because the planner had no column statistics to work with on a JSONB field.

The rule of thumb: if you will ever filter, sort, or join on a field, it belongs in a dedicated normalized column. JSONB is appropriate only for genuinely dynamic, schema-varying data that you retrieve as a whole object — event payloads, user-defined form responses, or configuration blobs where the shape of the data legitimately varies from row to row.

Naming Conventions: Consistency the AI Will Not Provide#

AI agents apply no consistent naming convention unless you explicitly specify one. Left unconstrained, an agent will mix userId, user_id, and UserID across the same schema — a patchwork that breaks ORM auto-mapping, confuses query builders, and causes silent bugs in case-sensitive databases.

Put your naming conventions in a CLAUDE.md or project rules file. Every AI session that generates schema code will then follow them automatically, rather than inventing its own convention from session to session.

How AI Agents Get Schema Design Wrong#

Schema design is one of the places where AI agents are most likely to produce something that looks correct and runs without errors but is structurally wrong. These failure patterns are consistent and predictable — which means you can check for them systematically on every AI-generated schema before it touches production.

Your Schema as a Spec#

The most important practice when working with AI agents on data modeling is to treat the schema as your specification — the most important architectural document you produce before any code is generated. Code changes are cheap; schema migrations on a production database with live data are expensive and risky. A missed constraint or a wrong relationship type is easy to fix in development and painful to fix after launch.

The review step in this workflow is not optional. An AI agent that generates a schema without constraints produces a database that accepts invalid data silently — and those silent failures surface as data integrity bugs weeks or months later, in production, when they are hardest to diagnose and most expensive to fix.

Summary#

Sources:

• Introduction of ER Model - GeeksforGeeks
• Crow's Foot Notation - FreeCodeCamp
• Primary and Foreign Key Constraints - Microsoft Learn
• Relationships in SQL - GeeksforGeeks
• When To Avoid JSONB In A PostgreSQL Schema - Heap
• Seven Essential Database Schema Best Practices - Fivetran
• 10 Rules for a Better SQL Schema - Sisense
• Experimenting With Generative AI For Database Design - Medium
• Why Your AI Coding Assistant Keeps Doing It Wrong - Pete Hodgson