Database schema design review before you migrate

You are a senior database engineer. Review the schema below before it is created in production.

Database: [e.g. "PostgreSQL 16", "MySQL 8", "SQLite", "SQL Server"]
What the application does: [ONE-PARAGRAPH DESCRIPTION]
Expected scale: [ROWS PER TABLE, WRITES PER SECOND, GROWTH]
Most important queries: [LIST 3-8 QUERIES IN SQL OR PLAIN ENGLISH]
Existing data to migrate: [YES/NO, AND FROM WHERE]

Schema:
"""
[PASTE CREATE TABLE STATEMENTS OR AN ORM MODEL FILE]
"""

Review in this order and label each finding HIGH, MEDIUM or LOW:
1. Correctness: primary keys, foreign keys, NOT NULL, UNIQUE and CHECK constraints that the business rules require.
2. Modeling: normalization problems, repeated data, many-to-many relationships, soft deletes, history and auditing needs.
3. Data types: money, time zones, text length, enums, IDs.
4. Indexes: which of my queries each index serves; missing and redundant indexes.
5. Naming and consistency.
6. Migration and operations: locking risks, backfills, defaults on large tables.

For each finding, give the reason and the corrected DDL. Finish with a revised full schema and a list of questions for me where the right answer depends on business rules you cannot see.

Example (excerpt):
HIGH - orders.total is FLOAT. Floating-point values can round money incorrectly. Use NUMERIC(12,2) or store integer cents.
HIGH - order_items has no foreign key to orders, so orphan rows are possible.
  ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id);
MEDIUM - The query "orders for a customer, newest first" needs an index on (customer_id, created_at DESC).
Question for you: can a customer be deleted while orders exist, or should orders keep a snapshot of the customer name?

When to use it

Use this prompt before running the first migration for a new feature or service, while changes are still cheap. It is also useful when you inherit a schema and want to know where the risks are, or when an ORM generated tables you have never really looked at.

The review is a second opinion, not a replacement for someone who knows your data. Treat HIGH findings as items to discuss with your team, and test any suggested change against realistic data volumes before relying on it.

How to customize it

• [DATABASE] matters because types, index options and locking behavior differ between engines and versions.
• [WHAT THE APPLICATION DOES] gives the model the business rules that constraints should enforce.
• [EXPECTED SCALE] decides whether an index or partitioning question is urgent or theoretical.
• [MOST IMPORTANT QUERIES] is the most valuable input. Indexes only make sense against real access patterns.
• [EXISTING DATA] brings migration risks, such as adding a NOT NULL column to a full table, into the review.

Why it works

The fixed order puts correctness before performance, which mirrors how experienced reviewers work: a missing constraint corrupts data silently, while a missing index is usually visible and fixable later. Severity labels help you decide what to fix now and what can wait.

Tying every index to a named query prevents the common habit of indexing every column, which slows down writes. Asking for corrected DDL makes each finding actionable, and the closing questions acknowledge that some decisions, like what happens when a customer is deleted, are business choices rather than technical ones.

Tips and variations

• Run the revised schema in a local database before trusting it; models sometimes produce syntax for the wrong engine version.
• Ask for an “expand and contract” migration sequence if the table is already live and cannot be locked for long.
• Combine it with our SQL query prompt to check that your key queries are easy to write against the new design.
• Use the code review checklist for the application code that reads and writes the new tables.