Top 7 Database schema design best practices

Database schema design forms the bedrock of any application's data architecture. A well-designed schema does more than just maintain data integrity and eliminate redundancy—it plays a vital role in how easily your database scales as your app grows.

In this guide, we dive into the top 7 database schema design best practices that developers actually rely on in their day-to-day software development journey.

1. Pick the Right Key Strategy (Auto-Increment vs. UUID)

Primary keys are the foundation of your schema—every foreign key, index, and query relies on them. Changing a primary key strategy late in production carries an extreme retrofit cost.

When choosing a primary key, keep two major factors in mind:

Width & Capacity

Defaulting to a standard 32-bit INT tops out at ~2.1 billion rows (2,147,483,647). Many production teams have suffered outages when busy tables hit this limit mid-flight. Standardize on BIGINT for identity columns by default—storage is cheap, but INT-to-BIGINT table migrations are notoriously painful.

Natural vs. Surrogate vs. UUID

Avoid using business data (emails, national IDs) as primary keys—business rules change, and primary keys shouldn't. Choose surrogate keys:

BIGINT Identity: Ideal for single-instance databases with tight write performance needs.
Time-Ordered UUIDs (UUIDv7): Perfect for distributed systems, microservices, or client-generated IDs. Standard random UUIDv4 scatters inserts across indexes and degrades cache performance; time-ordered UUIDv7 maintains index locality while preventing ID enumeration attacks.

-- Identity column (BIGINT default)
CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL
);

-- Time-ordered UUID (PostgreSQL 18+ or app-generated v7)
CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT uuidv7(),
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

2. Choose Precise Data Types (And Avoid Float for Money)

Altering column types on multi-million row tables requires long locks, full table rewrites, or complex online migration tooling. Getting your data types right on day one saves immense operational overhead.

Essential Rules for Data Types:

Never use FLOAT/REAL for financial data: Floating-point numbers cannot represent decimals like 0.1 precisely, leading to rounding errors. Always use DECIMAL/NUMERIC or store amounts in integer cents.
Always prefer TIMESTAMPTZ over TIMESTAMP: Plain TIMESTAMP records wall-clock time without context; the moment your server timezone changes, data context vanishes. TIMESTAMPTZ stores UTC under the hood at zero extra performance cost.
Don't cargo-cult VARCHAR(255): In modern databases like PostgreSQL, VARCHAR(n) and TEXT perform identically. Specify size bounds only when the business domain requires it (e.g., a 3-character IATA airport code).

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,                        -- Elastic string storage
    price DECIMAL(10, 2) NOT NULL,          -- Exact precision for currency
    weight REAL,                            -- Floating point is fine for measurements
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, -- Explicit timezone safety
    metadata JSONB                          -- Flexible attributes
);

3. Normalize for Integrity, Denormalize for Speed

A common mantra in production engineering is: Normalize until it hurts, denormalize until it works.

Aim for Third Normal Form (3NF) by default to eliminate duplicate data and prevent data anomalies. When business entities are separated cleanly, updating a record happens in exactly one place.

-- Normalization: Separate customers, orders, and line items
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(order_id),
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    price_at_time_of_order DECIMAL(10, 2) NOT NULL, -- Business rule: historical price snapshot
    UNIQUE(order_id, product_id)
);

4. Enforce Foreign Keys and Explicit Constraints

Application code changes frequently, but database constraints guarantee integrity regardless of which application service, admin script, or background worker touches the data.

Enforce Integrity at the Schema Layer:

Foreign Keys: Prevent orphaned records and broken references. (Pro-tip: PostgreSQL does not automatically index referencing columns—always index foreign key columns explicitly to prevent full table scans during parent row deletes).
Check Constraints: Validate field boundaries directly in the database (e.g., verifying status values or ensuring quantities are positive).
Nullability: Mark columns as NOT NULL by default unless missing data represents a genuine, expected business state.

CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL,
    
    -- Constraints enforce business logic at the storage layer
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT ck_users_username_length CHECK (char_length(username) >= 3),
    CONSTRAINT ck_users_status CHECK (status IN ('active', 'inactive', 'suspended', 'pending'))
);

CREATE TABLE employees (
    employee_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    department_id BIGINT NOT NULL,
    CONSTRAINT fk_department 
        FOREIGN KEY (department_id) 
        REFERENCES departments(department_id) 
        ON DELETE RESTRICT
);

-- Index foreign keys explicitly (required for Postgres performance)
CREATE INDEX idx_employees_department ON employees(department_id);

5. Establish Consistent Naming Conventions

Inconsistent schema naming breeds developer friction, leads to subtle bugs, and complicates ORM integration. Standardize early and enforce your rules consistently across all tables, columns, and indexes.

Key Rules for Naming Conventions:

Table Naming: Choose singular (user, order) or plural (users, orders) and stick with it universally. Plural is common for collections of resources, but singular matches domain model classes.
Casing: Use snake_case for SQL identifiers (created_at, user_id). It is the native standard across PostgreSQL, MySQL, and SQLite, avoiding quotes and cross-platform case-sensitivity issues.
Explicit Foreign Key References: Name foreign keys using the pattern [referenced_singular_table]_[referenced_primary_key]. For example, user_id referencing users(id).
Standardized Index Names: Name indexes explicitly so they can be identified easily during migration rollbacks or deadlock logs (e.g., idx_table_column for standard B-tree indexes, uq_table_column for unique constraints).

-- Clean, self-documenting naming structure
CREATE TABLE user_profiles (
    user_profile_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    avatar_url TEXT,
    bio TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_user_profiles_user 
        FOREIGN KEY (user_id) 
        REFERENCES users(user_id) 
        ON DELETE CASCADE
);

-- Clear, predictable index naming
CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

6. Index for Common Query Patterns (Without Over-Indexing)

Indexes are B-tree data structures that speed up read operations by providing fast, direct lookups. However, every index comes with a write penalty: whenever a row is inserted, updated, or deleted, every corresponding index on that table must also be updated.

Indexing Strategy:

Index Foreign Keys: Always index foreign key columns to optimize JOIN operations and prevent locking bottlenecks during parent deletes.
Cover Frequent WHERE and ORDER BY Filters: Place indexes on columns that appear frequently in search predicates, high-cardinality filters, or sorting operations.
Left-to-Right Column Ordering in Composite Indexes: In multi-column indexes, place the column with the highest selectivity (or the one filtered most often) first.
Prune Unused Indexes: Monitor database telemetry for unused or duplicate indexes and drop them to free up memory and speed up write operations.

-- Query pattern: Searching active orders for a specific customer sorted by date
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 42 AND status = 'active'
ORDER BY order_date DESC;

-- Optimal composite index matching the WHERE and ORDER BY sequence
CREATE INDEX idx_orders_customer_status_date 
ON orders (customer_id, status, order_date DESC);

7. Plan for Schema Evolution and Soft Deletes

A database schema is never truly static. As applications mature, business requirements shift, features are added, and data cleanup strategies become mandatory. Designing for change from day one prevents costly downtime and data loss.

Essential Rules for Long-Term Maintenance:

Use Schema Migration Tools: Never run raw ALTER TABLE commands manually in production. Track schema changes in versioned migration files using tools like Liquibase, Flyway, Prisma, or Goose.
Design Non-Breaking Changes: When modifying existing columns in production, perform changes safely using phased releases (e.g., add a new column as nullable, copy data asynchronously, update app logic to write to both, and finally deprecate the old column).
Handle Soft Deletes Carefully: Deleting rows permanently (HARD DELETE) can cause cascade issues and compliance problems. Using a deleted_at TIMESTAMPTZ column retains historical data, but be aware that it can break standard UNIQUE constraints unless handled with partial indexes.

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    deleted_at TIMESTAMPTZ DEFAULT NULL
);

-- Partial unique index: SKU must be unique ONLY among active products
CREATE UNIQUE INDEX uq_products_active_sku 
ON products(sku) 
WHERE deleted_at IS NULL;

Conclusion

Designing a robust database schema isn't just about setting up tables—it's about laying a resilient foundation that supports your application's growth, maintains strict data integrity, and minimizes future maintenance headaches.

By picking the right primary keys, selecting precise data types, adhering to proper normalization, enforcing constraints at the database level, following consistent naming conventions, indexing strategically, and planning for seamless schema evolution, you insulate your team against the most common and costly production pitfalls.

Applying these 7 best practices early in your software development lifecycle ensures your database remains fast, maintainable, and ready to scale alongside your user base.

StackRender