Database schema decisions outlive almost every other architectural choice in a software project. Frameworks come and go, APIs get rewritten, but the tables you create today will be migrated around and worked with for years. Getting the foundation right — entities, constraints, naming, and indexes — saves far more time than clever application-layer abstractions.
What changed in 2026
- Postgres dominates for relational workloads; MySQL/MariaDB still runs in legacy stacks but new projects mostly choose Postgres.
pgvector is now a first-class extension — AI-adjacent apps store embeddings in the same Postgres database as relational data, making schema design even more important.- Schema-as-code via Drizzle, Prisma, or Atlas is standard; raw SQL migrations are still the most portable format but ORM schema files are common.
- Multi-tenancy patterns (row-level security, separate schemas per tenant) are well-established in Postgres; schema design must account for this early.
- Generated columns and partial indexes in Postgres are widely used — worth knowing for performance-sensitive schemas.
Step 1 — identify entities and relationships
Before writing a single SQL statement:
- List the nouns in your domain (User, Order, Product, Invoice).
- Identify relationships (User places many Orders; Order contains many Products).
- Determine cardinality: one-to-one, one-to-many, many-to-many.
- Sketch an ERD (even on paper).
Many-to-many relationships always need a junction table:
-- Users and Roles: many-to-many
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE user_roles (
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id)
);
Naming conventions
| Convention |
Example |
Notes |
| Table names — plural nouns |
orders, line_items |
Consistent with most ORMs |
| Column names — snake_case |
created_at, user_id |
Standard in Postgres ecosystem |
FK columns — <table_singular>_id |
user_id, order_id |
Unambiguous reference |
Boolean columns — is_* or has_* |
is_active, has_verified |
Clear intent |
| Timestamp columns |
created_at, updated_at |
Always include both |
Pick one convention and enforce it across the entire schema.
Primary keys — surrogate vs natural
| Type |
Example |
Pros |
Cons |
BIGSERIAL |
1, 2, 3… |
Compact, fast index, readable |
Sequential, guessable |
UUID v4 |
f47ac10b-... |
Non-guessable, safe to expose |
Larger (16 bytes), random index inserts |
UUID v7 |
time-ordered UUID |
Non-guessable + sortable by time |
Newer, less ORM support |
| Natural key |
email, slug |
No extra column |
Changes when business data changes |
Recommendation in 2026: UUID v7 for new schemas where you want a non-guessable sortable key; BIGSERIAL when compactness matters more than opacity.
-- Postgres 17+ UUID v7 (via extension)
CREATE EXTENSION IF NOT EXISTS "pg_uuidv7";
CREATE TABLE articles (
id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
title TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Constraints — enforce data integrity at the DB level
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
NOT NULL everywhere data must exist — nullable columns demand null-checks throughout application code.CHECK for enum-like fields; consider a Postgres ENUM type for truly static value sets.ON DELETE RESTRICT vs CASCADE: RESTRICT prevents orphaning; CASCADE cleans up. Pick deliberately.
Indexing strategy
-- Always index FK columns (Postgres does NOT auto-index FKs)
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Composite index for common queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Partial index for active records only
CREATE INDEX idx_users_active ON users(email) WHERE is_active = true;
-- Index for text search
CREATE INDEX idx_articles_title ON articles USING gin(to_tsvector('english', title));
How to pick: normalize vs denormalize
| Situation |
Recommendation |
| OLTP (write-heavy, many small transactions) |
Normalize to 3NF — reduce duplication |
| Reporting / analytics queries |
Denormalize or use a materialized view |
| Rarely changing reference data |
Lookup table (normalized) |
| Data that is queried together always |
Consider a single table with columns |
| Frequently aggregated totals |
Consider a denormalized counter column |
Start normalized; denormalize only with a measured query to justify it.
Common mistakes
No updated_at column. Add it from the start; retrofitting it means a migration on a large table.
Storing money as FLOAT. Floating-point arithmetic introduces rounding errors. Store as BIGINT cents or use NUMERIC(19,4).
Text columns with no length consideration. TEXT is fine in Postgres (no performance cost vs VARCHAR(n)) — but if there is a business rule (email max length, slug max length), add a CHECK constraint.
Not indexing foreign keys. In Postgres, a FK does not get an index automatically. Every unindexed FK is a latent table scan in production.
Over-indexing write-heavy tables. Each index slows down inserts and updates. Profile before adding; indexes on columns never in WHERE/JOIN clauses waste space.
What to skip
- EAV (Entity-Attribute-Value) tables for structured data — they are notoriously hard to query and maintain. Use JSONB columns for truly semi-structured data; use real columns for structured data.
- Varchar lengths as validation in Postgres —
VARCHAR(255) has no performance benefit over TEXT; validate length in application code or with a CHECK constraint.
created_by as a username string — reference the user's primary key; names change.
FAQ
Should I use UUIDs or integers for primary keys?
Both are fine. UUIDs are better when you need to generate IDs outside the database (client-side, distributed systems). Integers are more compact and slightly faster. UUID v7 gives you time-ordering with opacity.
How many columns is too many for one table?
No hard rule. If a subset of columns are always NULL for half the rows, that suggests a subtype that belongs in a separate table. If every column is used for every row, wide tables are fine.
When should I use JSONB columns?
For genuinely semi-structured data where the schema varies per row — product attributes, user preferences, plugin configs. Do not use JSONB to avoid defining a schema you are too lazy to design.
How do I handle soft deletes?
Add deleted_at TIMESTAMPTZ (null means not deleted) and use partial indexes plus RLS policies to filter them. Soft deletes complicate unique constraints — you may need partial unique indexes.
Where to go next