AI Prompt for Database Schema Design

Design robust database schemas with this AI prompt: normalized tables, keys, constraints, workload-driven indexes, and complete DDL scripts.

The prompt

Role: You are a database architect who designs schemas that stay clean as applications grow.

Context: Dialect: [DIALECT]. Domain: [DOMAIN_DESCRIPTION]. Entities: [ENTITIES]. Expected scale: [SCALE]. Key access patterns: [ACCESS_PATTERNS, e.g. 'list orders by customer', 'search books by title'].

Task: 1) Design the tables: names, columns with types, primary keys, foreign keys, and constraints (NOT NULL, UNIQUE, CHECK where valuable). 2) Explain the normalization decisions — where you normalized and where you deliberately denormalized, and why. 3) Recommend indexes driven by [ACCESS_PATTERNS] (exact CREATE INDEX statements). 4) Flag future-proofing choices: what will be painful to change later (e.g. using email as PK, money as float). 5) Provide the full DDL.

Format: Design overview first, then table-by-table definitions, then normalization rationale, then indexes, then the complete DDL script.

Ask the AI to write three realistic queries against the new schema covering your main access patterns, proving the design serves actual reads before you commit to the DDL.

Constraints: Justify every denormalization. Use appropriate types for [DIALECT] (no VARCHAR(255) by default, no float for money). Include audit columns (created_at/updated_at) where sensible.

Variables to fill in

Replace each [variable] in the prompt with your own details:

[DIALECT] [DOMAIN_DESCRIPTION] [ENTITIES] [SCALE] [ACCESS_PATTERNS]

Example input

DOMAIN=online bookstore, ENTITIES=books, authors, customers, orders, reviews, SCALE=100k books, 1M orders/year, NEEDS=full-text search, order history, inventory tracking

How to use this prompt

List your entities AND planned query patterns — schema design follows access patterns. State the scale; it changes indexing and normalization choices.

Tips for the best results

Challenge the normalization choices — ask what breaks if you denormalize further.
Ask which decisions are hardest to reverse after launch; get those right first.
Have it generate seed data SQL to test the schema immediately.