1. Home
  2. Coding & data
  3. Database Schema Design from Requirements

Database Schema Design from Requirements

Turns your feature requirements into a structured relational schema.

Coding & data Planning & analysis Multi-step Technique · ChainingTechnique · Have it ask backTechnique · Specify the output format
Prompt · 3 variables

You are a senior backend engineer with extensive experience in relational data modeling. Instead of dumping a finished schema all at once, guide me through the design step by step, explaining the rationale as we build it together.

I am building {{service description}}, and the database will be {{database system}}. This is the initial version (MVP), and features may expand later.

Based on the feature list below, design the database schema across three distinct steps. Step 1: Identify all necessary entities (people, items, events) with just names and one-line descriptions. Step 2: Define relationships between entities and the architectural reasoning behind them. Step 3: Provide the final detailed table definitions. Stop after completing each step, and only proceed to the next step when I reply "Next".

Before starting Step 1, ask up to 3 clarifying questions if any critical business logic or assumptions are missing that could fundamentally alter the schema design.

For Step 3, present each table as a four-column markdown table: "Column Name · Data Type · Constraints (PK, FK, NULL/NOT NULL) · Description". Below each table, include a one-line explanation of why this table must exist separately.

Do not invent tables for features not explicitly listed. If something might be useful in the future, list it separately under a "Future Considerations" section at the very end. Use lowercase letters and snake_case for all table and column names.

Main key features: """ {{main key feature}} """

Copy, then paste here · ChatGPT and Claude open with the prompt filled in Open in ChatGPT ↗Open in Claude ↗Open in Gemini ↗ Edit in builder Download classroom card

Why it is written this way

Role
You are a senior backend engineer with extensive experience in relational data modeling. Instead of dumping a finished schema all at once, guide me through the design step by step, explaining the rationale as we build it together.
Context
I am building {{service description}}, and the database will be {{database system}}. This is the initial version (MVP), and features may expand later.
Steps
Based on the feature list below, design the database schema across three distinct steps. Step 1: Identify all necessary entities (people, items, events) with just names and one-line descriptions. Step 2: Define relationships between entities and the architectural reasoning behind them. Step 3: Provide the final detailed table definitions. Stop after completing each step, and only proceed to the next step when I reply "Next".
Ask back
Before starting Step 1, ask up to 3 clarifying questions if any critical business logic or assumptions are missing that could fundamentally alter the schema design.
Format
For Step 3, present each table as a four-column markdown table: "Column Name · Data Type · Constraints (PK, FK, NULL/NOT NULL) · Description". Below each table, include a one-line explanation of why this table must exist separately.
Constraints
Do not invent tables for features not explicitly listed. If something might be useful in the future, list it separately under a "Future Considerations" section at the very end. Use lowercase letters and snake_case for all table and column names.
Input
Main key features: """ {{main key feature}} """

When you ask an AI to design a database, it usually dumps a massive wall of table definitions at once. It might look plausible at a glance, but you lose the chance to ask why a specific column belongs in a specific table, and finding structural flaws requires auditing dozens of columns from scratch.

Breaking the task down into staged steps—Entities → Relationships → Table Definitions—allows you to inspect and correct foundational issues early. If "Appointments" and "Service History" are bundled into one entity in Step 1, you can fix it immediately before any downstream tables are defined. Without an explicit rule to pause after each step, the AI will generate all three steps in a single response.

Including clarifying questions prevents flawed architectural assumptions. Whether a cancelled booking should be hard-deleted or soft-deleted, or whether a user can access multiple salon locations, significantly changes the schema. A proactive AI asks these questions instead of making silent assumptions. Capping the limit at 3 prevents an endless interrogation.

Enforcing a strict format with types and constraints ensures that the output is immediately actionable code rather than vague conceptual notes. The constraint "Do not invent tables for unlisted features" keeps the AI from automatically injecting boilerplate bloat like notifications, audit logs, or tag systems.

Unfamiliar terms? See Aha AI: prompt, chain-of-thought

Compared with a bad example

Common bad example

I want to make a salon booking app. Design the database tables.

The AI immediately dumps ten tables with dozens of columns without asking for clarification. You have no way to verify edge cases, unrequested features (loyalty points, coupons, tier badges) get silently included, and actual core requirements like no-show tracking are completely omitted. Modifying one table breaks foreign keys across the entire output, forcing you to start over.

Variations

Auditing an Existing Schema

Auditing an Existing Schema

Review the {{database system}} table schema we just designed and point out only potential architectural flaws, bottlenecks, or edge-case risks. Organize your feedback into a table with the columns: "Location · Issue · Failure Scenario · Recommended Fix". List at most 5 items, sorted by severity. Ignore subjective naming preferences.

Use this as a follow-up prompt in the same thread. Setting a hard limit of 5 items and excluding naming style debates prevents trivial nitpicks from burying critical structural flaws.

Reverse-Engineering from UI/Screens

Reverse-Engineering from UI/Screens

I am building {{service description}}. Based on the feature list below, analyze what each user screen needs to display and organize the data requirements into a table with the columns: "Feature · Screen Data Requirements · Data Source / Origin". Do not write any table schemas yet. Once the table is complete, ask if any screen-level data is missing.

""" {{main key feature}} """

Useful when requirements are still rough. Mapping out the exact UI data requirements before touching the schema makes the subsequent database modeling significantly more accurate.

Model notes

After receiving the response for Step 1, simply send "Next" to continue. If continuing in a new session, copy and paste the previous step results.

Because this is a multi-step conversational prompt, the context window grows. If Step 3 starts deviating from earlier decisions, paste the finalized entity list from Steps 1 and 2 directly into your prompt. If you need an ERD diagram, request relationships as plain-text tables or Mermaid diagrams; ASCII line drawings generated by LLMs often render misaligned relationships.

Related prompts

Establish Clean-up Rules for Messy Tabular Data
Standardize inconsistent table columns systematically before changing values.
Coding & data Multi-step ChainingSelf-checkShow an example
API Integration Code Generator from Docs
Paste API documentation to get fully authenticated, working code.
Coding & data Fill and adapt Have it ask backFence off the inputConstraints
Variable and Function Naming Candidates
Describe what your code does and compare naming options before choosing.
Coding & data Ready to use Show an exampleSpecify the output formatConstraints
Clause-by-Clause SQL Query Explanation
Explains complex SQL queries step-by-step in logical order of execution.
Coding & data Ready to use Assign a roleGive contextSpecify the output format
Build a Repetitive Task Automation Script in Python
Turn manual file sorting and data workflows into automated Python scripts
Coding & data Multi-step Have it ask backChainingGive context
Excel Formula Builder and Error Fixer
Turn plain English calculation requirements into accurate Excel formulas.
Coding & data Fill and adapt Give contextAsk for revisionsSpecify the output format

Last updated 2026-09-02 · Found a mistake? Let us know