Back to prompts
CodingChatGPTClaudeGemini

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

Try it with

ChatGPTClaudeGemini

Related Prompts

Coding

Code Review Assistant

Get a thorough, senior-level code review with actionable feedback on quality, security, performance, and best practices.

ChatGPTClaudeGemini
Coding

Debugging Detective

Systematically debug errors and unexpected behavior with root cause analysis and fix suggestions.

ChatGPTClaudeGemini
Coding

Code Refactoring Advisor

Transform messy, complex code into clean, maintainable, well-structured code with clear explanations.

ChatGPTClaudeGemini