Web Application Database Schema Design
A database schema is the foundation that is hardest to change after launch. Improperly normalized tables, missing FKs, or incorrect data types turn into technical debt that accumulates for years. We've encountered this dozens of times: when a project grows and the database starts cracking at the seams, redesign costs many times more. On one project, the client lost two months due to missing indexes — queries took 30 seconds. After implementing a proper schema, everything flew in 50 milliseconds. On average, schema refactoring takes 2–3 days but prevents months of rework. We guarantee that after our work, queries will be tens of times faster.
Main Principles and Common Problems
In 80% of projects, we find the same errors: N+1 queries due to missing foreign keys, index fragmentation when using random UUIDs, loss of precision in financial calculations due to FLOAT, and magic numbers instead of enums. For most applications, the third normal form (3NF) is sufficient: each field depends only on the primary key, without transitive dependencies. Normalization helps avoid redundancy and update anomalies.
Denormalization is justified for counters (comments_count, likes_count) — instead of COUNT JOIN on every query. Also for cached aggregates (monthly order totals) and flattening hierarchical data for search. It is harmful when personal data or frequently changing statuses are duplicated.
Why Choosing the Right Data Type Is a Critical Decision
Errors in data types are one of the most common causes of production issues. Here are typical antipatterns:
-- Bad
user_id INT -- will overflow at 2.1 billion records
price FLOAT -- precision loss in financial calculations
status INT -- magic numbers, no domain constraint
created VARCHAR(30) -- string sorting instead of dates
settings TEXT -- no structure, no index
-- Good
user_id BIGINT -- or UUID
price DECIMAL(12, 2) -- exact arithmetic
status VARCHAR(20) CHECK (status IN ('draft', 'published', 'archived'))
created TIMESTAMPTZ -- with timezone
settings JSONB -- structured, indexable
TIMESTAMPTZ stores time in UTC and converts on read according to the session's TimeZone. TIMESTAMP stores "as-is" — when the server timezone changes, data loses meaning.
How to Choose a Primary Key: BIGSERIAL or UUID?
-- SERIAL (auto-increment): simple, compact (8 bytes), predictable
id BIGSERIAL PRIMARY KEY
-- UUID v4: globally unique, but 16 bytes, random order = index fragmentation
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- ULID via pg_ulid or application-side generation:
-- lexicographically sortable by time, 16 bytes
id UUID PRIMARY KEY DEFAULT uuid_generate_v7() -- PostgreSQL 17+
For most web applications, BIGSERIAL is the optimal choice. UUID is needed when IDs are generated on the client side or to hide predictability.
How to Avoid Common Schema Design Mistakes
The key to success is to think ahead about access patterns. If the application frequently reads the cart with items, use aggregate fields and avoid deep JOINs. For historical data (orders), intentionally denormalize unit_price to preserve a price snapshot. Always check whether the chosen PK supports growth — BIGSERIAL covers 9.2 quintillion records, enough for decades.
Example: E-commerce Schema
CREATE TABLE categories (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
slug VARCHAR(220) NOT NULL UNIQUE,
parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL,
sort_order INT NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
title VARCHAR(500) NOT NULL,
slug VARCHAR(520) NOT NULL UNIQUE,
category_id BIGINT NOT NULL REFERENCES categories(id) ON DELETE RESTRICT,
price DECIMAL(12, 2) NOT NULL CHECK (price > 0),
status VARCHAR(20) NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft', 'published', 'archived')),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
specs JSONB,
search_vector TSVECTOR, -- for full-text search
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled')),
total DECIMAL(12, 2) NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'USD',
meta JSONB, -- delivery address etc.
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(12, 2) NOT NULL, -- price at time of purchase
UNIQUE (order_id, product_id)
);
unit_price is intentional denormalization: the product price will change over time, but the historical price in the order must remain unchanged.
ON DELETE RESTRICT vs CASCADE — rule: CASCADE only when child records are meaningless without the parent (order_items without order). RESTRICT when deleting the parent should be explicitly prevented (cannot delete a category with products).
Which Indexes to Create Immediately and Soft Delete
Add these immediately when creating the schema:
-- FK columns — always, otherwise DELETE parent = seq scan on child table
CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);
-- Frequent filters
CREATE INDEX idx_products_status_created ON products (status, created_at DESC);
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
-- Partial index for active records
CREATE INDEX idx_products_published ON products (category_id, created_at DESC)
WHERE status = 'published';
-- GIN for JSONB
CREATE INDEX idx_products_specs ON products USING GIN (specs);
Soft delete pattern:
-- Soft delete
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMPTZ;
CREATE INDEX idx_products_deleted_at ON products (deleted_at) WHERE deleted_at IS NULL;
-- Audit table
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
row_id BIGINT NOT NULL,
operation CHAR(1) NOT NULL CHECK (operation IN ('I', 'U', 'D')),
old_data JSONB,
new_data JSONB,
changed_by BIGINT REFERENCES users(id),
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Partial index with WHERE deleted_at IS NULL — active records are indexed separately. Deleted records do not enter the index and do not slow down queries.
Our Process and What's Included
- Analysis — identify business entities, relationships, frequent queries.
- Design — build an ER diagram, normalize, choose data types.
- DDL Creation — SQL scripts with indexes, FKs, constraints.
- Documentation — schema description, comments, developer guide.
- Audit — review existing schema, identify issues, provide recommendations.
The deliverable includes: ER diagram (up to 15 tables) in PlantUML or Draw.io format, SQL DDL with indexes and constraints, schema documentation (README with table and field descriptions), migration recommendations, and one week of consultation after delivery.
Timelines
Schema design for a new project (up to 15 tables): 1–2 days. Review and refactoring of an existing schema: 1–3 days. The cost is calculated individually — contact us for an estimate. Order schema design — we'll take all nuances into account. Get a professional consultation even if you're unsure about the scope.