The database schema is the foundation everything else in your application is built on. Your API shapes data from the database. Your frontend displays it. Your business logic manipulates it. Get the schema wrong and you're fighting the data model in every layer of the application.
Unlike code, which can be refactored relatively cheaply, a live database schema is difficult to change. Adding a column is easy. Removing one that's in use is risky. Splitting a table that has millions of rows and foreign key dependencies requires careful migration planning. Changing a data type on a heavily indexed column can require hours of downtime.
This guide covers the principles and patterns that lead to schemas that hold up as products grow.
SponsoredCheck out today's featured offer →Start With Your Entities and Relationships
The first step in schema design is identifying the core entities in your system and how they relate to each other. An entity is a distinct type of thing your application manages: users, products, orders, organizations, messages, documents.
For each entity, ask:
- What data does it hold?
- What other entities does it connect to?
- What's the cardinality of each relationship? (one-to-one, one-to-many, many-to-many)
Map these out on paper or a whiteboard before writing any SQL. An entity-relationship diagram (ERD) — even a rough one — surfaces ambiguities before they become bugs.
Common relationship patterns:
One-to-many: One user has many orders. Implement with a foreign key on the "many" side.
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
total_cents INTEGER NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
Many-to-many: Users can be members of many organizations; organizations have many members. Implement with a join table.
CREATE TABLE organization_members (
organization_id INTEGER NOT NULL REFERENCES organizations(id),
user_id INTEGER NOT NULL REFERENCES users(id),
role TEXT NOT NULL DEFAULT 'member',
joined_at TIMESTAMP NOT NULL DEFAULT NOW(),
PRIMARY KEY (organization_id, user_id)
);
One-to-one: A user has one profile. Implement with a foreign key and unique constraint.
CREATE TABLE user_profiles (
user_id INTEGER PRIMARY KEY REFERENCES users(id),
bio TEXT,
avatar_url TEXT,
website TEXT
);
Choose the Right Data Types
Data types enforce integrity and affect storage and performance.
IDs: SERIAL or BIGSERIAL for auto-incrementing integers. UUID for distributed systems where IDs are generated at the application layer. UUIDs are larger (16 bytes vs 4–8 bytes) and slower to index, but they're globally unique and don't expose enumeration information.
Text: TEXT is fine for most strings in PostgreSQL — there's no performance advantage to VARCHAR(n) in Postgres unless you actually want to enforce a length limit. Use VARCHAR(n) when you want the database to enforce a maximum length.
Numbers: Use INTEGER for counts and small numbers. BIGINT for IDs in large tables (> 2 billion rows). For money, never use FLOAT or DOUBLE — floating point is inexact and will produce rounding errors in financial calculations. Use INTEGER to store the amount in the smallest currency unit (cents, pence) or a NUMERIC(precision, scale) type.
Timestamps: Use TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ in PostgreSQL) for all timestamps. Always store in UTC. Dealing with timezones at the application layer, after the fact, is genuinely painful.
Booleans: Use BOOLEAN. Don't use INTEGER or CHAR — it adds confusion and bypasses type checking.
JSON: JSONB in PostgreSQL is useful for semi-structured data — configuration objects, metadata, flexible attributes that vary by record type. Don't abuse it as a workaround for designing a proper schema. If you find yourself querying deeply into JSON fields frequently, consider normalizing the data into columns.
Normalize to Reduce Redundancy
Normalization is the process of organizing your schema to minimize data redundancy and dependency. The key insight: if the same data appears in multiple places, it can become inconsistent.
The three most practical normalization rules for application developers:
1NF (First Normal Form): Every column holds a single value. No arrays of values in a single field (use a separate table instead). Every row is uniquely identifiable.
2NF: Every non-key column depends on the entire primary key, not just part of it. (Mostly relevant for tables with composite primary keys.)
3NF: Every non-key column depends only on the primary key, not on other non-key columns. If column B depends on column A and column A is not the primary key, that's a violation — A and B belong in a separate table.
In practice: if you're duplicating data (storing the same email address in both a users table and every orders row), that's a smell. Store the data once and reference it.
That said, strategic denormalization is sometimes appropriate for read performance. A materialized summary column (like a total_comment_count on a post that's updated via trigger) avoids an expensive COUNT query on every page load. The trade-off is write complexity and the possibility of inconsistency.
Index for the Queries You Actually Run
Indexes dramatically speed up reads but slow down writes and consume disk space. Index strategically.
Index foreign keys. PostgreSQL does not automatically index foreign keys. Every foreign key column that you JOIN or filter on should have an index.
CREATE INDEX idx_orders_user_id ON orders(user_id);
Index columns used in WHERE clauses. If you frequently filter by a column, index it.
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_sessions_token ON sessions(token);
Use composite indexes for multi-column filters. If you always filter by organization_id AND status together, a composite index is more efficient than two separate indexes.
CREATE INDEX idx_projects_org_status ON projects(organization_id, status);
Index columns used in ORDER BY. If you frequently sort by created_at DESC, indexing that column speeds up sorted queries.
Be wary of over-indexing. Every index slows down INSERT, UPDATE, and DELETE on that table. Tables with very high write volume need fewer, more carefully chosen indexes.
Use Soft Deletes Carefully
Soft deletes mark a record as deleted (with a deleted_at timestamp) instead of removing it from the database. This lets you:
- Recover accidentally deleted records
- Preserve audit trails
- Handle foreign key dependencies gracefully
The downside: every query must filter out soft-deleted records. Missing this filter is a common bug. If you use soft deletes, enforce them via a database view or ORM scope that always applies the filter.
-- Add to any table that needs soft delete
deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL;
-- Create a view that hides deleted records
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
Add Audit Fields to Every Table
At minimum, add created_at and updated_at to every table. These are invaluable for debugging, reporting, and supporting customer questions.
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
Update updated_at automatically via a trigger or ORM hook. Never rely on application code to remember to set it.
For tables with high auditing requirements (financial transactions, permission changes), consider a separate audit log table that captures every state change with the user who made it and the timestamp.
Manage Schema Changes With Migrations
Never alter a production database schema manually. Use a migration system so that schema changes are versioned, repeatable, and reversible.
Popular migration tools by ecosystem:
- Drizzle (TypeScript/Node.js) — generates SQL migrations from your TypeScript schema definitions
- Prisma (TypeScript/Node.js) — schema-first with automatic migration generation
- Alembic (Python/SQLAlchemy) — mature migration tool for Python apps
- Flyway / Liquibase (JVM) — popular in enterprise Java environments
Every schema change should be a migration file in version control, reviewed like any other code change, and applied through a deployment process — not hand-typed into a production console.
Common Patterns Worth Knowing
Slug-based URLs: For user-facing URLs like /blog/my-post-title, add a slug column with a unique constraint. Generate it from the title at creation time and never change it (or handle redirects if you do).
Hierarchical data: For categories, comment threads, or org charts, use a parent_id self-referential foreign key for simple trees, or a materialized path / nested sets approach for deep trees with frequent traversal.
Status fields: Use an ENUM type (or a constrained text column with a check constraint) for fields with a defined set of values — order statuses, content states, user roles. This prevents invalid values from sneaking in.
Configuration and settings: A settings JSONB column on entity tables (users, organizations) is a practical escape hatch for per-entity configuration that would otherwise require many nullable columns or a complex key-value table.
Getting your schema right is one of the highest-leverage investments you can make in a new product. A well-designed schema makes every other layer of the application easier to build and maintain. A poorly designed one creates problems that accumulate silently until they become very expensive to fix.
When you're designing the schema for a new product, Blueprint AI's database schema section generates a detailed entity model with table definitions, field types, and relationships — giving your team a concrete starting point for review and refinement.
