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

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

At a glanceRestaurant reservation schema: four tablesPostgreSQL 16 schema for about 50 restaurants and 200,000 bookings a year
  1. restaurantsOne venue, with name and timezone
  2. dining_tablesOne physical table, label unique per restaurant, 1-20 seats
  3. customersOne person who books, unique email
  4. reservationsOne table for a time slot, status booked to no_show

Example for: a restaurant reservation feature on PostgreSQL 16, about 50 restaurants and 200,000 bookings a year.

1. Entities
  • restaurants: one venue.
  • dining_tables: one physical table in a venue.
  • customers: one person who books.
  • reservations: one booking of one table for a time slot.
2. Columns
TableColumnTypeKeyConstraints
dining_tablesrestaurant_idBIGINTFKNOT NULL, label unique per restaurant
dining_tablesseatsSMALLINTCHECK 1-20
customersemailTEXTNOT NULL, UNIQUE (one account per email)
reservationstable_idBIGINTFKNOT NULL, ON DELETE RESTRICT
reservationsparty_sizeSMALLINTCHECK > 0
reservationsstarts_at / ends_atTIMESTAMPTZCHECK ends_at > starts_at
reservationsstatusTEXTCHECK in booked, seated, cancelled, no_show
3. SQL
CREATE TABLE restaurants (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  timezone TEXT NOT NULL
);
CREATE TABLE dining_tables (
  id BIGSERIAL PRIMARY KEY,
  restaurant_id BIGINT NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
  label TEXT NOT NULL,
  seats SMALLINT NOT NULL CHECK (seats BETWEEN 1 AND 20),
  UNIQUE (restaurant_id, label)
);
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
CREATE TABLE reservations (
  id BIGSERIAL PRIMARY KEY,
  table_id BIGINT NOT NULL REFERENCES dining_tables(id) ON DELETE RESTRICT,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  party_size SMALLINT NOT NULL CHECK (party_size > 0),
  starts_at TIMESTAMPTZ NOT NULL,
  ends_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL DEFAULT 'booked'
    CHECK (status IN ('booked','seated','cancelled','no_show')),
  CHECK (ends_at > starts_at)
);
CREATE INDEX ON dining_tables (restaurant_id);
CREATE INDEX ON reservations (table_id, starts_at);
CREATE INDEX ON reservations (customer_id);
4. Supported queries
  1. Tonight's bookings for one venue:
SELECT r.* FROM reservations r JOIN dining_tables t ON t.id = r.table_id
WHERE t.restaurant_id = 7 AND r.starts_at::date = CURRENT_DATE;
  1. A customer's upcoming bookings:
SELECT * FROM reservations WHERE customer_id = 42 AND starts_at > now();
  1. No-show count per customer:
SELECT customer_id, COUNT(*) FROM reservations
WHERE status = 'no_show' GROUP BY customer_id;
  1. Free tables for 4 guests at 19:00-21:00:
SELECT t.id FROM dining_tables t WHERE t.restaurant_id = 7 AND t.seats >= 4
AND NOT EXISTS (SELECT 1 FROM reservations r WHERE r.table_id = t.id
  AND r.status <> 'cancelled'
  AND r.starts_at < '2025-06-14 21:00+02' AND r.ends_at > '2025-06-14 19:00+02');
5. Trade-offs
  • Double-booking is only blocked by app logic; an EXCLUDE constraint with btree_gist would enforce it in the database.
  • One table per reservation means large parties needing joined tables are not supported.
  • Status as TEXT with CHECK is easy to change but lacks a history of status changes.
  • RESTRICT on customers blocks account deletion until bookings are archived.

Assumptions: fixed slot lengths, no deposits or waitlist; unclear whether walk-ins need a customer record.

Written by AI from this prompt. Your results will vary.

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 in Coding