Database Schema Design Best Practices for 2026

Quick answer

Good database schema design is the foundation of every reliable application. Get it right and your queries are fast, your migrations are painless, and your team can reason about the data model without a decoder ring. Get it wrong and you spend years working around decisions that were made in an afternoon.

This guide covers 12 concrete best practices for relational database design in 2026, with SQL examples for both PostgreSQL and MySQL. Each practice includes the reasoning behind it, the common mistake it prevents, and code you can adapt for your own projects.

Why schema design matters more than query tuning

You can optimize queries, add indexes, and throw hardware at a database. But if the schema itself is poorly structured -- wrong data types, missing constraints, tangled relationships -- every optimization is a patch on a cracked foundation.

A well-designed schema:

• Makes correct queries easy to write and incorrect queries hard to write.
• Reduces the surface area for bugs by encoding business rules as constraints.
• Makes query optimization straightforward because the data is already organized for the access patterns you need.
• Simplifies onboarding because the schema documents the domain.

The best time to fix your schema is before you ship. The second best time is now.

Start with an ERD before writing any SQL

An entity-relationship diagram (ERD) is the blueprint of your database. Before writing CREATE TABLE, sketch the entities, their attributes, and the relationships between them. This catches design problems when they are cheap to fix -- on a whiteboard, not in a migration.

An ERD answers three questions at a glance:

1. What entities exist? (users, orders, products, invoices)
2. How do they relate? (one-to-many, many-to-many, one-to-one)
3. What are the cardinalities? (one user has many orders, one order has many line items)

You do not need expensive modeling tools. A whiteboard works for brainstorming. For ongoing work, a tool that generates an ERD from your live schema saves time and stays in sync with reality. QueryDeck auto-generates ERDs from your connected database, so the diagram always reflects the actual state of your schema -- not a stale Lucidchart from six months ago.

The key insight: treat the ERD as a living document, not a one-time artifact.

1. Use consistent, lowercase naming conventions

Naming is the most visible part of your schema and the most common source of confusion. Pick a convention and enforce it everywhere.

The convention that causes the least friction:

• Table names: snake_case, plural (users, order_items, product_categories)
• Column names: snake_case, singular (first_name, created_at, is_active)
• Indexes: idx_{table}_{columns} (idx_users_email, idx_orders_status_created_at)
• Foreign keys: {referenced_table_singular}_id (user_id, order_id)
• Constraints: {table}_{type}_{columns} (users_uq_email, orders_ck_status)

PostgreSQL folds unquoted identifiers to lowercase. MySQL identifier case sensitivity depends on the operating system and lower_case_table_names setting. Using lowercase everywhere avoids surprises on both platforms.

-- Good
CREATE TABLE order_items (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12,2) NOT NULL
);

-- Bad: mixed case, abbreviated, inconsistent
CREATE TABLE OrderItem (
  OI_ID    INT PRIMARY KEY,
  ord_id   INT,
  ProdID   INT,
  qty      INT,
  price    DECIMAL(10,2)
);

Common mistake: Abbreviating column names to save typing. qty instead of quantity, cust_addr instead of customer_address. You type a column name once; you read it a thousand times. Spell it out.

2. Choose the right primary key strategy

Every table needs a primary key. The two main options are auto-incrementing integers and UUIDs. Each has real trade-offs.

Auto-incrementing integers (SERIAL / IDENTITY / AUTO_INCREMENT)

-- PostgreSQL (modern syntax)
CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- MySQL
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

Pros: Small (8 bytes for BIGINT), fast to index, natural ordering by insertion time, human-readable.

Cons: Exposes row count and insertion order. Not safe to use as external identifiers (enumeration attacks). Cannot generate IDs client-side or in distributed systems.

UUIDs

-- PostgreSQL
CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- MySQL 8+
CREATE TABLE users (
  id BINARY(16) DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

Pros: Globally unique. Safe to expose externally. IDs can be generated by clients or multiple database nodes without coordination.

Cons: Larger (16 bytes). Random UUIDs fragment B-tree indexes, hurting write performance on large tables. Not human-readable.

Recommendation for 2026: Use UUIDv7 where available. UUIDv7 is time-ordered, which preserves B-tree locality while retaining global uniqueness. PostgreSQL 17+ supports uuidv7() natively. For MySQL, generate UUIDv7 in your application layer.

If you do not need external-facing IDs or distributed generation, BIGINT GENERATED ALWAYS AS IDENTITY remains the simplest and most performant choice.

3. Normalize first, denormalize with intent

Normalization (3NF) eliminates data redundancy and ensures consistency. Start there. Every table should have a clear purpose: one entity, one table.

-- Normalized: separate tables for orders and customers
CREATE TABLE customers (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total       NUMERIC(12,2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

When to denormalize

Denormalization is not a design failure -- it is a deliberate trade-off. You accept data redundancy to avoid expensive joins at read time. Denormalize when:

• Read-heavy analytics tables need pre-aggregated data.
• Materialized views or read replicas serve dashboards.
• A specific join is the bottleneck and the data changes infrequently.

-- Denormalized: store customer_name on order for fast display
CREATE TABLE orders (
  id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id    BIGINT NOT NULL REFERENCES customers(id),
  customer_name  TEXT NOT NULL,  -- denormalized for read performance
  total          NUMERIC(12,2) NOT NULL,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

The rule: Normalize by default. Denormalize with a comment explaining why. If you cannot articulate the specific query or access pattern that requires denormalization, you do not need it yet.

4. Define foreign keys and enforce referential integrity

Foreign keys are not optional metadata. They are constraints that prevent orphaned records, document relationships, and enable the query planner to make better join decisions.

CREATE TABLE line_items (
  id         BIGINT GENERATED ALWAYS AS IDENTITY 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)
);

`ON DELETE CASCADE` -- when an order is deleted, its line items are deleted automatically. Use for child records that have no meaning without the parent.

`ON DELETE RESTRICT` -- prevent deleting a product that has existing line items. Use when the referenced record must survive independently.

`ON DELETE SET NULL` -- set the foreign key to NULL. Use when the relationship is optional and the child record should survive.

Common mistake: Skipping foreign keys "for performance." The write overhead of foreign key checks is negligible in modern databases. The cost of debugging orphaned records is not.

5. Add indexes strategically, not everywhere

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, and DELETE must maintain every index on the table. The goal is to have exactly the indexes your queries need, and no more.

Index the columns you filter, join, and sort on

-- Single-column index for equality lookups
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Composite index for queries that filter on status and sort by date
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

-- Partial index for a common filter (PostgreSQL)
CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'pending';

MySQL equivalent for the composite index

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

MySQL 8+ supports descending index key parts, though the optimizer may not always use them for backward scans.

Column order in composite indexes matters

Put the most selective (highest cardinality) column first when all columns use equality filters. If the query uses a range condition (>, <, BETWEEN) on one column, put that column last.

-- Query: WHERE status = 'pending' AND created_at > '2026-01-01'
-- Best index: (status, created_at) -- equality first, range last
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

Common mistake: Creating an index on every column "just in case." This bloats storage, slows writes, and confuses the query planner. Profile your actual queries first. A tool with a schema browser helps you see existing indexes at a glance before creating duplicates.

For deeper guidance on index strategy and query performance, see SQL query optimization tips.

6. Use appropriate data types

Choosing the right data type is a constraint that enforces correctness at the storage layer.

Critical rule for money: Never use floating-point types (FLOAT, DOUBLE) for monetary values. Floating-point arithmetic introduces rounding errors. Use NUMERIC (PostgreSQL) or DECIMAL (MySQL) with explicit precision.

-- PostgreSQL
CREATE TABLE invoices (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount NUMERIC(12,2) NOT NULL CHECK (amount >= 0),
  currency CHAR(3) NOT NULL DEFAULT 'USD'
);

-- MySQL
CREATE TABLE invoices (
  id     BIGINT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(12,2) NOT NULL CHECK (amount >= 0),
  currency CHAR(3) NOT NULL DEFAULT 'USD'
);

7. Handle enums carefully

Enums appear simple but can become migration headaches. There are three approaches, each with trade-offs.

Approach 1: Database-level ENUM type (PostgreSQL)

CREATE TYPE order_status AS ENUM ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

Pros: Type-safe, storage-efficient (4 bytes), self-documenting. Cons: Adding values requires ALTER TYPE ... ADD VALUE, which cannot run inside a transaction in PostgreSQL. Removing or renaming values is painful.

Approach 2: CHECK constraint

-- PostgreSQL
CREATE TABLE orders (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))
);

Pros: No custom type to manage. Easy to modify with ALTER TABLE ... DROP CONSTRAINT / ADD CONSTRAINT. Cons: Slightly less discoverable than a named type. No reuse across tables without repeating the constraint.

Approach 3: Lookup table

CREATE TABLE order_statuses (
  id   SMALLINT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

INSERT INTO order_statuses (id, name) VALUES
  (1, 'pending'), (2, 'confirmed'), (3, 'shipped'),
  (4, 'delivered'), (5, 'cancelled');

CREATE TABLE orders (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status_id SMALLINT NOT NULL REFERENCES order_statuses(id)
);

Pros: Easy to add or rename values. Works identically on PostgreSQL and MySQL. Queryable. Cons: Requires a join to get the human-readable value.

Recommendation: For small, stable sets (under 10 values that rarely change), use a CHECK constraint. For larger or frequently changing sets, use a lookup table. Avoid database-level ENUMs unless your team has a well-tested migration process.

8. Add timestamps and audit fields to every table

Every table should record when rows were created and last modified. This is non-negotiable for debugging, auditing, and data analysis.

-- PostgreSQL
CREATE TABLE products (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  price      NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Auto-update updated_at on every change
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_products_updated_at
  BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- MySQL
CREATE TABLE products (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(255) NOT NULL,
  price      DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

MySQL handles updated_at natively with ON UPDATE CURRENT_TIMESTAMP. PostgreSQL requires a trigger.

For tables where you need to track *who* made changes, add created_by and updated_by columns that reference a users table or store an application-level identifier.

9. Implement soft deletes when data retention matters

Soft deletes mark rows as deleted without removing them from the table. This preserves audit trails and enables undo functionality.

CREATE TABLE customers (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Query only active customers
SELECT * FROM customers WHERE deleted_at IS NULL;

-- "Delete" a customer
UPDATE customers SET deleted_at = now() WHERE id = 42;

Make soft deletes safe with a partial unique index

A common problem: after soft-deleting a user with email = 'alice@example.com', you cannot create a new user with the same email because the unique constraint still covers the soft-deleted row.

-- PostgreSQL: unique constraint only on active rows
CREATE UNIQUE INDEX idx_customers_email_active
  ON customers (email) WHERE deleted_at IS NULL;

MySQL does not support partial indexes. The workaround is to include deleted_at in a composite unique index or move deleted records to an archive table.

When to skip soft deletes: If the data has no audit requirement and the table is write-heavy, hard deletes with a separate audit log table are cleaner.

10. Use JSON columns sparingly and with constraints

Both PostgreSQL (JSONB) and MySQL (JSON) support JSON columns. They are useful for semi-structured data that varies between rows -- user preferences, third-party webhook payloads, feature flags. They are not a replacement for relational modeling.

-- PostgreSQL: JSONB with a check constraint
CREATE TABLE user_preferences (
  user_id    BIGINT PRIMARY KEY REFERENCES users(id),
  settings   JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT valid_settings CHECK (jsonb_typeof(settings) = 'object')
);

-- Query a specific key
SELECT user_id, settings->>'theme' AS theme
FROM user_preferences
WHERE settings->>'theme' = 'dark';

-- Index for fast JSON key lookups (PostgreSQL)
CREATE INDEX idx_user_preferences_settings ON user_preferences USING gin (settings);

-- MySQL: JSON column
CREATE TABLE user_preferences (
  user_id    BIGINT PRIMARY KEY,
  settings   JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT valid_settings CHECK (JSON_TYPE(settings) = 'OBJECT')
);

-- Query a specific key (MySQL)
SELECT user_id, JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme')) AS theme
FROM user_preferences
WHERE JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme')) = 'dark';

Rules for JSON columns:

1. If you query a JSON key in a WHERE clause regularly, it should probably be a real column.
2. If the JSON structure is consistent across all rows, it should be a real table.
3. Use JSONB (PostgreSQL) over JSON -- JSONB is stored in a decomposed binary format that is faster to query and supports GIN indexes.
4. Always add a CHECK constraint to validate the JSON structure.

11. Plan your migration strategy from day one

Schema changes are inevitable. How you manage them determines whether deployments are routine or terrifying.

Use a migration tool

Every project should use a migration tool that tracks schema changes as numbered, version-controlled files. Options include:

• Flyway (Java, SQL-based, works with PostgreSQL and MySQL)
• Alembic (Python / SQLAlchemy)
• Prisma Migrate (TypeScript / Node.js)
• dbmate (standalone binary, database-agnostic)
• golang-migrate (Go)

Write reversible migrations

Every migration should have an up and a down. If a deploy goes wrong, you need to roll back without data loss.

-- Migration 003_add_phone_to_users.up.sql
ALTER TABLE users ADD COLUMN phone TEXT;

-- Migration 003_add_phone_to_users.down.sql
ALTER TABLE users DROP COLUMN phone;

Avoid destructive migrations on large tables

On tables with millions of rows, ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT ... can lock the table for minutes in MySQL. PostgreSQL 11+ handles this without a rewrite for most default values, but always test against a staging environment with production-scale data.

Safe migration pattern for adding a NOT NULL column:

-- Step 1: Add column as nullable
ALTER TABLE orders ADD COLUMN priority TEXT;

-- Step 2: Backfill in batches
UPDATE orders SET priority = 'normal' WHERE priority IS NULL AND id BETWEEN 1 AND 100000;
-- Repeat for remaining batches...

-- Step 3: Add the NOT NULL constraint
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;

-- Step 4: Add the default for future rows
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 'normal';

This approach avoids long locks and lets you monitor progress.

12. Document your schema in the schema itself

The best documentation lives where the code lives. Use comments, constraints, and meaningful names to make the schema self-documenting.

-- PostgreSQL
COMMENT ON TABLE orders IS 'Customer purchase orders. One order can have many line_items.';
COMMENT ON COLUMN orders.status IS 'Current order status. Valid values: pending, confirmed, shipped, delivered, cancelled.';
COMMENT ON COLUMN orders.total IS 'Order total in the currency specified by currency_code. Excludes tax.';

-- MySQL
ALTER TABLE orders COMMENT = 'Customer purchase orders. One order can have many line_items.';
ALTER TABLE orders MODIFY COLUMN status VARCHAR(20) NOT NULL
  COMMENT 'Current order status: pending, confirmed, shipped, delivered, cancelled';

When you explore a database with a schema browser, these comments surface directly alongside the table and column definitions. That context is invaluable for new team members and for your future self.

Common schema design mistakes and how to avoid them

Putting it all together: a production-ready schema example

Here is a condensed example that applies all 12 practices to a simple e-commerce domain:

-- PostgreSQL example

CREATE TABLE customers (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL,
  phone      TEXT,
  deleted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX idx_customers_email_active
  ON customers (email) WHERE deleted_at IS NULL;

CREATE TABLE products (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       TEXT NOT NULL,
  sku        TEXT NOT NULL UNIQUE,
  price      NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  is_active  BOOLEAN NOT NULL DEFAULT true,
  metadata   JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status      TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')),
  total       NUMERIC(12,2) NOT NULL CHECK (total >= 0),
  currency    CHAR(3) NOT NULL DEFAULT 'USD',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

CREATE TABLE order_items (
  id         BIGINT GENERATED ALWAYS AS IDENTITY 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 NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

COMMENT ON TABLE customers IS 'Registered customers. Supports soft delete via deleted_at.';
COMMENT ON TABLE orders IS 'Customer purchase orders with status tracking.';
COMMENT ON TABLE order_items IS 'Individual line items within an order.';

This schema is normalized, constrained, indexed for common access patterns, and documented. You can visualize the relationships instantly by connecting this database to a tool with auto-generated ERD support and ORM auto-detection - no manual diagramming required.

Schema design is an ongoing practice

Database schema design is not a one-time event. Your schema evolves as your product evolves. The practices in this guide -- consistent naming, appropriate keys, normalization by default, strategic indexes, explicit constraints, and disciplined migrations -- reduce the cost of that evolution.

If you work with PostgreSQL or MySQL, having a database client with ORM auto-detection and SQL notebooks that lets you browse schemas, visualize relationships, and explore table structures makes these practices easier to follow day to day.

The best schema is the one your entire team can understand, query confidently, and change safely.