Design a Normalized Database Schema from a Plain Feature Description
Turns a plain-language feature description into a normalized relational schema with typed columns, keys, constraints, ready-to-run CREATE TABLE SQL, example queries and trade-offs.
You getEntities, column table with these columns and all CREATE TABLE and CREATE INDEX statements in one SQL…
Prompt
You are a senior backend engineer who designs relational database schemas for production apps.
I will paste a plain-language description of a feature right after this prompt. Before designing anything, ask me up to 3 short numbered questions in one message (for example: which database engine, expected row counts, must-have reports or screens) and wait for my answers. Then design a normalized schema for the feature.
Rules:
1. Normalize to third normal form. If you denormalize anything, name the column and give the reason.
2. Every table has a primary key. Every relationship has a foreign key with an explicit ON DELETE behavior.
3. Use concrete types for the chosen engine, such as TIMESTAMPTZ or NUMERIC(10,2). Never store money as FLOAT.
4. Add NOT NULL, UNIQUE and CHECK constraints wherever the description implies a business rule, and state which rule each one enforces.
5. Index every foreign key and every column filtered on in the supported queries.
Output format:
1. Entities: one line per table explaining what a row represents.
2. Column table with these columns: Table | Column | Type | Key | Constraints
3. All CREATE TABLE and CREATE INDEX statements in one SQL code block, in dependency order.
4. Supported queries: 4-6 numbered questions in plain English, each followed by its SQL.
5. Trade-offs: 3-5 bullets on what this design makes hard, slow or risky.
End with one line listing the assumptions you made and anything the description left unclear.Example output
Tips and common edits
Why this works
- Asking for engine and data volume first means types, indexes and trade-offs fit your actual database instead of generic SQL.
- Requiring explicit ON DELETE behavior and a named rule for each constraint forces decisions most quick schemas skip.
- Listing supported queries with SQL shows whether the design actually serves the feature before you write migrations.
Use this in ChatGPT, Claude or Gemini before writing migrations for a new feature. Have a short feature description ready, plus your database engine and rough data volumes for the follow-up questions.
Common edits
- Ask for a migration file format for your tool, such as Prisma, Django or Flyway, instead of raw SQL.
- Add a rule to include soft deletes or audit columns like created_at and updated_at on every table.
Prompt details
- By
- Prompts-GPT editors
- Published
Did this prompt work?
Keep exploring
More like this
Find and Fix Data Quality Issues with Ready-to-Run SQL Checks
Paste your schema and get SQL queries that catch duplicates, NULLs, orphaned foreign keys and out-of-range values, each with what a bad result means and a fix.
Translate SQL Between MySQL, PostgreSQL and SQL Server with a Change Table
Returns your query rewritten for the target database, plus a table flagging every function, date, quoting and LIMIT change and where behavior could differ.
SQL Query from Schema and Question
Writes a SQL query that answers your question from your schema, explains each clause and suggests indexes worth adding.
Optimize a Slow SQL Query Using Its EXPLAIN Plan Step by Step
Paste a slow query and its EXPLAIN output to get the costliest plan node named, exact CREATE INDEX or rewrite fixes, and a clear way to verify the speedup.
Explain a Complex SQL Query in Plain English, Step by Step
Get a plain-English walkthrough of any SQL query in execution order, with tiny sample tables, row counts after each clause, and the hidden gotchas.
Write SQL Window Function Queries for Running Totals, Ranks and Period Changes
Get a commented SQL window function query for running totals, rankings or period comparisons, with each PARTITION BY, ORDER BY, tie and NULL choice explained.