Claude/ChatGPT Prompt to Design a Normalized Database Schema

What this prompt does

This prompt drives ChatGPT to produce a complete, opinionated database design from a single structured request. By locking in the normalization level, ORM target, and specific query patterns upfront, you get output that actually reflects your constraints — not a generic textbook schema. The template forces the model to commit to relationship cardinality (1:1, 1:N, M:N with pivot tables named explicitly) and an index strategy tied to your actual query patterns, which is where most AI-generated schemas fall short.

The [requirements] variable is where you drop constraints that don't fit neatly into entity lists: multi-tenancy via a team_id on every table, soft deletes across all models, or a specific audit trail pattern. Keep it to 3–5 bullet points; more than that and the model starts trading depth for coverage.

The [optimization_goal] variable is the lever that separates a read-heavy reporting schema from a write-heavy transactional one. Feed it read throughput and you get covering composite indexes and suggestions for where to add summary columns. Feed it write concurrency and you get narrower tables, shorter transactions, and guidance on which FK constraints to enforce at the application layer rather than the database layer — particularly relevant when targeting MySQL, which does not support deferred constraints.

When to use it

• Starting a new SaaS product and need a schema baseline before the first migration
• Designing a pivot-heavy feature (multi-tenant permissions, tag systems, product variants) where relationship modeling is the hard part
• Onboarding a junior dev who needs a concrete, annotated migration file to learn from
• Prototyping a client project where you need a credible schema to discuss in a discovery call
• Generating realistic factory and seeder data for a test suite on a new domain model
• Getting a second opinion on relationship cardinality before committing a design to production

Example output

For [project_type] = SaaS subscription platform, [entities] = users, plans, subscriptions, invoices, [orm] = Laravel, [optimization_goal] = read throughput:

-- plans table
id          BIGINT UNSIGNED PK
name        VARCHAR(80) NOT NULL
price_cents INT UNSIGNED NOT NULL
interval    ENUM('month','year') NOT NULL   -- MySQL ENUM
INDEX idx_plans_interval (interval)

-- subscriptions table
id          BIGINT UNSIGNED PK
user_id     BIGINT UNSIGNED NOT NULL  -- FK users.id
plan_id     BIGINT UNSIGNED NOT NULL  -- FK plans.id
status      ENUM('active','paused','cancelled') NOT NULL
starts_at   TIMESTAMP NOT NULL
ends_at     TIMESTAMP NULL
INDEX idx_subs_user_status (user_id, status)  -- covers dashboard lookup
INDEX idx_subs_ends_at (ends_at)              -- covers expiry sweep job

The Laravel migration for subscriptions includes foreignId('user_id')->constrained()->cascadeOnDelete(). The accompanying UserFactory state withActiveSubscription() is generated inline, wiring the factory chain so User::factory()->withActiveSubscription()->create() produces a complete test fixture without additional setup.

Pro tips

• Set [normalization_level] to 3NF for transactional data and denormalized (star schema) for reporting pipelines — the model handles both confidently but needs explicit instruction to choose one over the other.
• Name your [query_patterns] specifically: user dashboard lookup by status, monthly invoice aggregation gets you targeted composite indexes. Vague patterns like typical web app produce generic single-column indexes that miss multi-column cardinality.
• After getting the schema, follow up with: "Show me the three most likely N+1 query paths and the eager-load fix for each" — the model already holds the full relationship map in context and gives precise with() chains rather than generic advice.
• For Laravel output, specify [orm] as Laravel 11 with $casts property (not casts() method) to match current conventions and avoid the deprecated casts() method that still appears in older training examples.
• The ER diagram description output is prose — paste it into Mermaid or dbdiagram.io immediately after receiving it. Modeling errors the model glosses over in text become obvious the moment you see a foreign key arrow pointing the wrong direction.