Schema Design Basics

A schema is the blueprint of your data: what tables exist, what columns they have, and how they relate. Good schema design is mostly common sense plus a few habits.

• One table per "thing." Users, posts, orders — each gets its own table.
• Every row needs a unique ID (a primary key).
• Link tables with foreign keys, not by copying data around.
• Pick honest types. Money is a decimal, not a float. Dates are timestamps, not text.
• Don't duplicate. If a user's email lives in five tables, you'll eventually have five different emails.

Here is a simple schema for a blog, the kind AI will generate for you:

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT NOT NULL UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_id   UUID NOT NULL REFERENCES users(id),
    title       TEXT NOT NULL,
    body        TEXT NOT NULL,
    published   BOOLEAN NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_posts_author ON posts(author_id);

That author_id ... REFERENCES users(id) line is the foreign key. It wires each post back to exactly one real user, so the database itself refuses to store a post with no valid author:

   users                          posts
   ┌─────────────┐                ┌─────────────┐
   │ id  (PK) ◀──┼────────────────┤ author_id   │  (FK → users.id)
   │ email       │   one user      │ id  (PK)    │
   │ created_at  │   has many      │ title       │
   └─────────────┘   posts         │ body        │
                                   │ published   │
        1  ────────────────────▶  *└─────────────┘
       (one)                    (many)

Notice the REFERENCES (a foreign key tying a post to a real user) and the NOT NULL and UNIQUE constraints. These are guardrails the database enforces for you, so bad data can't sneak in even when your code has a bug.

A constraint worth adding deliberately is what happens when the parent disappears. By default, deleting a user whose posts still reference them will fail — which is often what you want. But you can be explicit: REFERENCES users(id) ON DELETE CASCADE deletes the posts too, while ON DELETE RESTRICT blocks the deletion. Choosing on purpose beats discovering the default the hard way in production. The same "let the database protect you" instinct applies to a CHECK constraint (CHECK (price >= 0)) — it refuses nonsense data at the source instead of trusting every code path to validate.