Database Schema Design Assistant
Design a normalized, scalable database schema for your application with table definitions, relationships, and indexing strategy.
Prompt Template
You are a senior database architect. Design a database schema for the following application: Application type: [e.g., e-commerce platform, SaaS project management tool, social network, booking system] Database: [PostgreSQL / MySQL / SQLite / MongoDB] Scale: [small — thousands of rows / medium — millions / large — hundreds of millions+] Key entities: [list the main objects, e.g., users, products, orders, reviews] Key operations: [list most frequent queries, e.g., list all orders by user, find available slots, search products] Special requirements: [soft deletes, multi-tenancy, audit trail, etc.] Provide: 1. **Entity list with attributes** — all tables with their fields and data types 2. **Relationship diagram (text)** — show how tables relate (one-to-many, many-to-many) 3. **SQL CREATE statements** — ready-to-use for all tables 4. **Indexing strategy** — which columns to index and why 5. **Design decisions explained** — why you chose this structure 6. **Scale considerations** — what to change at 10x and 100x growth 7. **Common query examples** — SQL for the top 3 operations listed
Example Output
Schema Design: SaaS Project Management App
Entities:
- organizations (id, name, plan, created_at)
- users (id, org_id, email, role, created_at)
- projects (id, org_id, owner_id, name, status, archived_at)
- tasks (id, project_id, assignee_id, title, status, priority, due_date, created_at)
- comments (id, task_id, user_id, body, created_at)
- tags (id, org_id, name, color)
- task_tags (task_id, tag_id) — junction table
Key Relationships:
- organizations → users: one-to-many
- organizations → projects: one-to-many
- projects → tasks: one-to-many
- tasks → comments: one-to-many
- tasks ↔ tags: many-to-many via task_tags
SQL (PostgreSQL):
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
plan VARCHAR(50) DEFAULT 'free',
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
email VARCHAR(255) UNIQUE NOT NULL,
role VARCHAR(50) DEFAULT 'member',
created_at TIMESTAMPTZ DEFAULT NOW()
);
Indexing Strategy:
- tasks(project_id, status) — most common filter combination
- tasks(assignee_id, due_date) — for 'my tasks' views
- users(email) — login queries
Tips for Best Results
- 💡List your most frequent query patterns upfront — they determine indexing strategy more than anything else
- 💡Mention if you need soft deletes (deleted_at timestamp) or audit trails — these add columns to every table
- 💡Ask it to generate seed data SQL too, so you can test the schema immediately
Frequently Asked Questions
What is the Database Schema Design Assistant prompt?
Design a normalized, scalable database schema for your application with table definitions, relationships, and indexing strategy. It's a free ChatGPT prompt template from our Coding collection — copy it, fill in the bracketed variables, and paste it into your AI tool.
Which AI tools work with this prompt?
It's written and tested for ChatGPT, Claude and Gemini. Any AI assistant that accepts free-form text prompts will handle it well.
How do I customize this ChatGPT prompt?
Replace the bracketed variables — such as [PostgreSQL / MySQL / SQLite / MongoDB] — with your own details before running it. List your most frequent query patterns upfront — they determine indexing strategy more than anything else
Is this prompt free to use?
Yes. Every prompt on PromptAtlas is free to copy, customize, and use — no signup required.
Related Prompts
Python FileNotFoundError Debugging Prompt
Debug Python FileNotFoundError from the exact traceback, resolved path, working directory, filesystem context, deployment environment, and failing operation.
Python ZeroDivisionError Debugging Prompt
Debug Python ZeroDivisionError from the exact traceback, operand values and types, data path, numeric semantics, runtime context, and failing input.
Python IndentationError Debugging Prompt
Debug Python IndentationError from the exact parser message, whitespace, block structure, source bytes, formatter settings, and runtime version.
Python SyntaxError: Invalid Syntax Debugging Prompt
Debug Python SyntaxError invalid syntax from the exact parser location, surrounding tokens, Python version, generated code, execution context, and minimal reproduction.
Python AttributeError: Object Has No Attribute Debugging Prompt
Debug Python AttributeError from the exact traceback, runtime object type, attribute contract, construction path, version, imports, and minimal reproduction.
Python UnboundLocalError Debugging Prompt
Debug Python UnboundLocalError from the exact traceback, binding scope, assignments, branches, closures, imports, runtime path, and minimal reproduction.