What is a database schema review checklist?

A database schema review checklist is a structured list of checks that developers and DBAs run through before deploying a schema to production. It covers naming conventions, normalization, indexing, constraints, security, performance, and documentation.

How often should I review my database schema?

Review your schema before every major release, after significant migrations, and at least once per quarter for long-running projects. Automated tools like SchemaLens can catch drift between environments in CI/CD.

What are the most common schema design mistakes?

Common mistakes include: missing indexes on foreign keys, inconsistent naming conventions, lack of constraints (NOT NULL, UNIQUE, CHECK), storing sensitive data unencrypted, missing created_at/updated_at timestamps, and tables without primary keys.

Can SchemaLens automatically review my schema?

SchemaLens diffs two schemas and detects changes in tables, columns, indexes, constraints, triggers, views, and functions. It generates migration scripts, breaking-change warnings, and a risk score — giving you an automated second opinion on every schema change.

Database Schema Review Checklist — 42 Checks for Production-Ready SQL

🏷️ Naming Conventions

0/6

Consistent table naming (snake_case)

All tables use the same case convention. Mixed snake_case, camelCase, and PascalCase in one schema creates confusion.

Best Practice

Column names are descriptive and unabbreviated

Avoid abbreviations like usr_id, dt, or amt. Use user_id, created_at, amount_cents.

Best Practice

Foreign key columns match referenced primary key name

If users.id is the PK, the FK should be user_id (or users_id if your convention requires it). Mismatched names make JOINs harder to reason about.

Best Practice

Index names follow a consistent pattern

Use idx_<table>_<columns> or <table>_<columns>_idx. Consistency makes it easy to spot missing indexes in EXPLAIN output.

Best Practice

No reserved words used as identifiers

Avoid naming columns order, group, user, password, or index. These require quoting and cause subtle bugs in ORMs and query builders.

Critical

Timestamps follow a consistent naming convention

Pick created_at / updated_at or created_at / modified_at and stick to it everywhere. Mixed conventions break ORM automations.

Caution

📐 Normalization

0/6

No repeating groups in columns

Columns like tag_1, tag_2, tag_3 violate 1NF. Use a junction table instead.

Critical

Every table has a primary key

Tables without a primary key cannot be reliably updated or deleted by ORMs, and some replication setups require one.

Critical

No redundant data stored in multiple places

If users.full_name is derived from users.first_name and users.last_name, it is denormalized. Use a generated column or compute in the application.

Caution

Junction tables use composite primary keys

A many-to-many junction table should have a composite PK on the two foreign key columns. This prevents duplicate relationships.

Best Practice

Enum values use a lookup table (not CHECK constraints alone)

CHECK constraints are fast but cannot store metadata. A lookup table lets you add descriptions, ordering, and soft-delete statuses without schema changes.

Best Practice

JSON/JSONB columns are used only for truly schemaless data

Storing structured data in JSON that should be in columns defeats indexing, constraints, and type safety. Reserve JSON for extensibility and nested documents.

Caution

🔍 Indexing

0/6

Every foreign key has a supporting index

Without an index, foreign key checks, cascades, and JOINs become full table scans. This is the #1 performance killer in schema design.

Critical

Composite indexes follow column selectivity order

Put the most selective column first in a composite index. An index on (status, created_at) is useless if 90% of rows share the same status.

Caution

No duplicate or redundant indexes

An index on (a) is redundant if you already have (a, b). Duplicate indexes waste disk space and slow down writes.

Caution

Partial indexes are used for filtered queries

If you frequently query WHERE deleted_at IS NULL, a partial index on that condition is smaller and faster than indexing the entire table.

Best Practice

Covering indexes are considered for hot queries

If a query only selects columns that are in the index, the database can satisfy the query from the index alone — no table lookup needed.

Best Practice

GIN/GiST indexes for JSONB and full-text search

JSONB queries and tsvector full-text search require GIN or GiST indexes. B-tree indexes cannot help these query patterns.

Best Practice

🔐 Constraints & Keys

0/6

NOT NULL is used deliberately, not by accident

Every column should explicitly declare NULL or NOT NULL. Relying on defaults varies by dialect and creates portability issues.

Caution

UNIQUE constraints exist where business logic requires them

Emails, usernames, order numbers, and slugs should have UNIQUE constraints. Application-level uniqueness checks race under load.

Critical

CHECK constraints validate business rules

Use CHECK for range validation (price >= 0), enum-like constraints (status IN ('pending', 'paid')), and format checks (LENGTH(phone) >= 10).

Best Practice

Foreign keys have ON DELETE / ON UPDATE rules

Explicitly choose RESTRICT, CASCADE, SET NULL, or SET DEFAULT. The default varies by dialect and can surprise you in production.

Critical

Default values are semantically correct

DEFAULT 0 on a price field, DEFAULT '' on a JSON field, or DEFAULT '1970-01-01' on a timestamp are all dangerous. Use NULL or meaningful defaults.

Caution

No soft-delete columns without an index

If you use deleted_at for soft deletes, every query must filter on it. Without an index, deleted rows pollute your query plans.

Caution

🛡️ Security

0/6

Passwords and tokens are never stored in plain text

Use bcrypt, Argon2, or scrypt for passwords. API tokens should be hashed before storage. Plain text credentials are a compliance violation.

Critical

PII columns are identified and documented

Email, phone, address, SSN, and credit card columns should be flagged. Consider encryption at rest or column-level encryption for the most sensitive fields.

Critical

Row-level security (RLS) is enabled where appropriate

PostgreSQL RLS prevents users from reading or modifying rows they do not own. It is the last line of defense against authorization bugs.

Best Practice

Database roles follow least-privilege principle

The application user should not have CREATE, DROP, or ALTER privileges in production. Separate roles for migrations, reads, and writes.

Caution

Connection strings do not expose credentials in logs

Ensure connection string parsing and logging strips passwords. Many ORMs log the full connection string on startup by default.

Caution

Audit columns (created_at, updated_at) exist on all tables

Audit trails are required for compliance (SOC 2, GDPR) and for debugging production incidents. Every table should track when rows are created and modified.

Best Practice

⚡ Performance

0/6

VARCHAR length limits are justified

VARCHAR(255) is a cargo-cult default. Use realistic limits (VARCHAR(320) for emails, VARCHAR(2048) for URLs). Excessive limits waste memory in sort buffers.

Best Practice

Appropriate data types for numeric values

Money should use DECIMAL or NUMERIC, not FLOAT or DOUBLE. IDs should use BIGINT or UUID, not INT if you expect >2B rows.

Critical

Large text/blob columns are in separate tables if rarely queried

Storing TEXT or BLOB in the main table bloats row size and slows full-table scans. Split rarely-accessed large fields into a separate table.

Best Practice

Partitioning is considered for tables >10M rows

Time-series data, logs, and event tables should be partitioned by range. Partition pruning makes queries on recent data orders of magnitude faster.

Best Practice

Columns that are frequently sorted have indexes

Queries with ORDER BY created_at DESC need an index on created_at. Without it, the database sorts the entire result set in memory or on disk.

Caution

No N+1 query patterns are enabled by schema design

Schemas with deep nesting or polymorphic associations often force ORMs into N+1 queries. Use explicit foreign keys and avoid polymorphic associations where possible.

Caution

📝 Documentation

0/6

Every table has a COMMENT describing its purpose

Table comments (COMMENT ON TABLE) are the single most valuable documentation investment. They show up in schema explorers and data dictionaries.

Best Practice

Column comments explain non-obvious fields

A column named status needs a comment explaining what each value means. A column named score needs units and scale documented.

Best Practice

An ERD or schema diagram exists and is up to date

New developers should be able to understand table relationships at a glance. Tools like dbdiagram.io or SchemaLens can generate ERDs from SQL.

Best Practice

Migration history is tracked in version control

Every schema change should be a migration file in Git. Ad-hoc changes in production are untraceable and unrepeatable.

Critical

A data dictionary or schema catalog is maintained

Beyond comments, maintain a living document that lists tables, ownership, retention policies, and PII classifications. This is essential for GDPR and SOC 2.

Best Practice

Deprecation plan exists for columns marked for removal

Never drop a column in one deploy. Stop writing to it, null it out, monitor for reads, then remove it in a later migration. Document the timeline.

Caution

Database Schema Review Checklist

🏷️ Naming Conventions

Consistent table naming (snake_case)

Column names are descriptive and unabbreviated

Foreign key columns match referenced primary key name

Index names follow a consistent pattern

No reserved words used as identifiers

Timestamps follow a consistent naming convention

📐 Normalization

No repeating groups in columns

Every table has a primary key

No redundant data stored in multiple places

Junction tables use composite primary keys

Enum values use a lookup table (not CHECK constraints alone)

JSON/JSONB columns are used only for truly schemaless data

🔍 Indexing

Every foreign key has a supporting index

Composite indexes follow column selectivity order

No duplicate or redundant indexes

Partial indexes are used for filtered queries

Covering indexes are considered for hot queries

GIN/GiST indexes for JSONB and full-text search

🔐 Constraints & Keys

NOT NULL is used deliberately, not by accident

UNIQUE constraints exist where business logic requires them

CHECK constraints validate business rules

Foreign keys have ON DELETE / ON UPDATE rules

Default values are semantically correct

No soft-delete columns without an index

🛡️ Security

Passwords and tokens are never stored in plain text

PII columns are identified and documented

Row-level security (RLS) is enabled where appropriate

Database roles follow least-privilege principle

Connection strings do not expose credentials in logs

Audit columns (created_at, updated_at) exist on all tables

⚡ Performance

VARCHAR length limits are justified

Appropriate data types for numeric values

Large text/blob columns are in separate tables if rarely queried

Partitioning is considered for tables >10M rows

Columns that are frequently sorted have indexes

No N+1 query patterns are enabled by schema design

📝 Documentation

Every table has a COMMENT describing its purpose

Column comments explain non-obvious fields

An ERD or schema diagram exists and is up to date

Migration history is tracked in version control

A data dictionary or schema catalog is maintained

Deprecation plan exists for columns marked for removal

Automate your schema reviews