Top 10 Database Schema Design Best Practices

Every list like this recites roughly the same ten practices. What the recitals skip is the dimension that actually matters in production: how expensive each mistake is to fix after the table holds real data. A missing index is a five-minute fix on a live system. A wrong primary key type is a multi-quarter migration project. Same list, very different stakes.

So here are the ten, each tagged with its retrofit cost. Spend your design attention proportionally: get the table-rewrite items right on day one, and stop agonizing over the ones you can fix any Tuesday.

1. Use Appropriate Primary Keys#

Retrofit cost: extreme. Every foreign key, index, and application query hangs off the primary key. Changing it later means touching all of them.

Two decisions here, and both have famous failure modes.

First, the width. A plain INT tops out at 2,147,483,647. Plenty of teams have watched inserts start failing when a busy table hit that ceiling, and GitLab spent literal years working through int-to-bigint conversions across its schema. Storage is cheap; the migration is not. Default to BIGINT unless you can prove the table stays small.

Second, natural vs. surrogate vs. UUID. Surrogate keys win most arguments because business data changes (emails get reassigned, national IDs get corrected) and a primary key must not. If you need IDs that are generatable by clients or safe across shards, use UUIDs, and prefer time-ordered UUIDv7 over random UUIDv4: random keys scatter inserts across the index and wreck cache locality on clustered-index engines like MySQL. PostgreSQL 18 ships uuidv7() natively.

-- Identity column, BIGINT by 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+; use an app-side v7 generator on older versions)
CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT uuidv7(),
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

2. Choose Appropriate Data Types#

Retrofit cost: high. Changing a column's type on a large table usually means a rewrite, a long lock, or an online-migration dance.

The recurring self-inflicted wounds, in descending order of pain:

• Money in floating point. REAL/FLOAT cannot represent 0.1 exactly; sums drift and accountants notice. Use DECIMAL/NUMERIC for anything you'd reconcile.
• TIMESTAMP instead of TIMESTAMPTZ. Plain timestamp records a wall-clock time with no zone; the meaning shifts with the server's timezone setting. In Postgres, timestamptz costs nothing extra and removes an entire bug class.
• IDs stored as text. Joins slow down, referential typos creep in, and the index bloats.
• VARCHAR(255) cargo cult. In Postgres, VARCHAR(n) and TEXT perform identically; pick a limit because the domain has one (an IATA code is 3 chars), not because a 2005 tutorial said 255.

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,          -- exact: it's money
    weight REAL,                            -- approximate is fine: it's a measurement
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    tags VARCHAR(50)[] DEFAULT '{}',
    metadata JSONB
);

3. Implement Proper Normalization#

Retrofit cost: high. Un-normalizing is a backfill; re-normalizing scattered duplicates is data archaeology.

Aim for Third Normal Form by default, then denormalize deliberately and locally: a cached order_total, a follower count, a reporting table. The rule of thumb that survives contact with production: normalize until it hurts, denormalize until it works, and write down which columns are derived so someone can rebuild them.

-- Instead of this (customer and product data duplicated into every row)
CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    product_price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL
);

-- Use this normalized shape
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,  -- deliberate: prices change, invoices don't
    UNIQUE(order_id, product_id)
);

Note price_at_time_of_order: that is not a normalization failure, it is a business rule. Knowing the difference is the whole skill.

4. Define Foreign Key Relationships#

Retrofit cost: moderate. Adding a foreign key later requires cleaning up the orphans that accumulated without it, and that cleanup is a data-quality project, not a DDL statement.

Foreign keys enforce referential integrity where it cannot be bypassed: in the database. Teams that drop them "for performance" or "because microservices" inherit the orphan cleanup and every join that silently returns fewer rows than expected. The overhead is real but usually mispriced; measure before you skip them.

One engine-specific trap: PostgreSQL does not automatically index the referencing column (MySQL/InnoDB does). Every DELETE on the parent then scans the child table while holding locks. If you declare a foreign key in Postgres, index it yourself.

CREATE TABLE employees (
    employee_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id BIGINT NOT NULL,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT   -- refuse to delete a department that still has employees
        ON UPDATE CASCADE
);

-- Postgres does not create this for you
CREATE INDEX idx_employees_department ON employees(department_id);

Be careful with ON DELETE CASCADE: it turns one mistaken delete into a silent multi-table purge. RESTRICT fails loudly, which is usually what you want.

5. Use Consistent Naming Conventions#

Retrofit cost: high, surprisingly. A rename is one statement in the database and a coordinated deploy everywhere else: application code, dashboards, ETL jobs, that one cron script nobody remembers. Renames are among the most incident-prone schema changes, so a name is effectively permanent. Choose accordingly.

Which convention matters far less than having exactly one. Pick, write it down, and enforce it with a lint rule rather than review-comment nagging:

-- One workable convention:
-- tables: plural snake_case; columns: singular; constraints/indexes: typed prefixes
CREATE TABLE payment_methods (
    payment_method_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT uq_payment_methods_name UNIQUE (name)
);

CREATE INDEX idx_payments_order_id ON payments(order_id);

6. Implement Constraints and Validation Rules#

Retrofit cost: moderate. You can add constraints later, but only after fixing every existing row that violates them, and there will be violating rows.

The database is the last line of defense that every code path, admin script, and future microservice must pass through. Application-level validation is a convenience; database constraints are a guarantee.

CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    status VARCHAR(20) NOT NULL,

    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'))
);

When you do retrofit a constraint onto a live table, Postgres has a two-step that avoids the long lock: add it as NOT VALID (new writes are checked immediately), then VALIDATE CONSTRAINT later with only a light lock while it scans existing rows.

Skip the regex email check you were about to write: the ground truth for an email address is whether it receives mail, and CHECK constraints that encode folklore regexes mostly reject valid addresses. NOT NULL and UNIQUE carry their weight; leave format validation to the application layer where you can update it without a migration.

7. Create Indexes#

Retrofit cost: low. This is the one you're allowed to fix later. CREATE INDEX CONCURRENTLY builds an index on a live Postgres table without blocking writes, which makes indexing the rare schema decision that is genuinely reversible.

That cuts both ways: don't pre-create speculative indexes at design time. Every index taxes every write and occupies cache; an unused one is pure cost. Index what the actual query patterns need, then audit with pg_stat_user_indexes and drop the dead weight.

-- Composite index for columns queried together (leftmost prefix matters)
CREATE INDEX CONCURRENTLY idx_orders_customer_date ON orders(customer_id, order_date);

-- Partial index when queries always carry the same predicate
CREATE INDEX CONCURRENTLY idx_orders_pending ON orders(order_date)
WHERE status = 'pending';

-- Find indexes nothing uses
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

8. Plan for Schema Evolution#

Retrofit cost: this item is the retrofit skill itself. The schema you ship is version one of dozens, and the practices that keep versions two through fifty boring are behavioral:

• Additive changes first. Add the new column, backfill, switch readers, then drop the old one (the expand-contract pattern). Never rename in place: a rename breaks every deployed reader in the same instant.
• Nullable or defaulted new columns. Since Postgres 11, ADD COLUMN ... DEFAULT on a big table is instant metadata; NOT NULL without a default on a populated table is a wall.
• Migrations in version control, one direction. Numbered, reviewed, immutable once merged. The schema_versions table you hand-roll is exactly what migration tooling maintains for you.

For the mechanics of doing this on a hot table without a maintenance window, see Postgres Schema Migration without Downtime.

9. Document Your Schema#

Retrofit cost: low to add, high to have skipped. The orders.state vs orders.status question gets asked once a week forever, or once, in a comment.

COMMENT ON TABLE customers IS 'Registered users; one row per verified account';
COMMENT ON COLUMN customers.email IS 'Primary contact email - unique, case-insensitive';

COMMENT ON beats a wiki because it lives with the schema, shows up in \d+ and every introspection tool, and survives the wiki reorg. The second half of documentation is keeping the DDL itself in git, where schema diffs show up in code review like any other change. Increasingly there is a third reader to write for: AI agents generating SQL against your schema see the comments and the DDL, not your wiki.

10. Implement Audit Trails#

Retrofit cost: impossible. Every other item on this list can be fixed later, painfully. History that wasn't recorded is simply gone, which is why this one makes the list despite being the least glamorous.

CREATE TABLE customer_audit (
    audit_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    action VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50) NOT NULL,
    old_data JSONB,
    new_data JSONB
);

CREATE OR REPLACE FUNCTION fn_audit_customer()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO customer_audit(customer_id, action, changed_by, new_data)
        VALUES (NEW.customer_id, TG_OP, current_user, to_jsonb(NEW));
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO customer_audit(customer_id, action, changed_by, old_data, new_data)
        VALUES (NEW.customer_id, TG_OP, current_user, to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO customer_audit(customer_id, action, changed_by, old_data)
        VALUES (OLD.customer_id, TG_OP, current_user, to_jsonb(OLD));
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_audit
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION fn_audit_customer();

Trigger-based auditing doubles the write cost on hot tables. Reserve it for the tables where "who changed this row" is a question auditors ask (accounts, permissions, money), and reach for change data capture when you need everything.

One closing observation from reviewing thousands of schema changes at Bytebase: the practices above rarely fail because someone didn't know them. They fail because change number 847, shipped at 6pm on a Friday, skipped review. A SQL review gate that mechanically checks the boring rules (missing index on a new foreign key, int primary key, plain timestamp) is how the list survives contact with a growing team.