Coding Workflows Workflow Intermediate

AI Database Design Workflow

Design a schema on its data, not a hunch — model the entities and relationships, set the constraints that protect integrity, plan indexes around real queries, then document the schema and migration.

The problem

A data model is the hardest thing in an application to change later — every query, every feature, and every migration inherits its shape. Get the entities or the relationships wrong and you pay for it in joins, workarounds, and risky migrations for the life of the product. 'Ask AI for a schema' gives you a confident set of tables that ignores your access patterns, skips the constraints that protect integrity, and indexes nothing. This workflow designs the model on its real data: the entities and how they relate, the constraints that keep it honest, the indexes the actual queries need, and a documented migration — before the schema is set in production.

Recommended workflow

Each step uses an existing NewPrompt tool, pre-filled by a matching resource. Open the resource to read it, or jump straight into the tool with the inputs ready.

  1. Model the entities and relationships

    Start with the data, not the tables. Anchor the model as an architect and map the real entities, their attributes, and how they relate — one-to-many, many-to-many, the optional versus the required — before anything becomes a column.

    Goal The core entities and their relationships mapped to the real domain.

    Resource Software Architect Role Prompt
    Tool Role Prompt Generator
    Open this step in Role Prompt Generator

  2. Set the constraints that protect integrity

    Decide what keeps the data honest: keys, uniqueness, nullability, foreign-key rules, and the invariants the application must never violate. Work them one decision at a time — the constraints are what stop bad data, not the code on top.

    Goal Keys, constraints, and integrity rules decided and justified.

    Resource Break a Complex Task into Prompt Steps
    Tool Multi-Step Prompt Builder
    Open this step in Multi-Step Prompt Builder

  3. Plan indexes around real queries

    Indexes follow access patterns, not guesses. Lay out the queries the app will actually run, then plan the indexes that serve them — and notice the ones missing that would turn a feature into a table scan.

    Goal Indexes planned against the queries the application really runs.

    Resource Missing Index Analysis — Which Indexes, at What Cost
    Tool SQL Optimization Prompt
    Open this step in SQL Optimization Prompt

  4. Document the schema and migration

    Capture the final model and the reasoning — why the relationships are shaped this way, what the constraints protect — plus the migration plan to get there, so the build and future-you start from a documented design.

    Goal The schema and its migration documented, reasoning included.

    Resource Technical Documentation Prompt
    Tool Markdown Output Builder
    Open this step in Markdown Output Builder

Expected outcome

A database schema designed on its real data — entities and relationships modeled, integrity constraints decided, indexes planned around the actual queries, and the whole thing documented with a migration plan — so you build on a deliberate model instead of a confident guess you'll be migrating around for years.

Best for

  • Designing a new schema before writing migrations
  • Modeling entities, relationships, and integrity constraints deliberately
  • Planning indexes around the queries an app will really run

Not for

  • Tuning a slow query on an existing schema — use a SQL optimization prompt
  • Designing the API over the data — use the AI API Design Workflow
  • Deciding the system's overall architecture — use the AI Project Architecture Workflow

FAQ

How is this different from optimizing a SQL query?

Query optimization speeds up a query against a schema that already exists. This designs the schema in the first place — entities, relationships, constraints, and the indexes that exist before any query is tuned. Design comes first; optimization is what you do later inside the model this produces.

How is this different from the AI API Design Workflow?

API design shapes the interface clients call; this shapes the data behind it. They inform each other — the contract and the schema — but they're distinct artifacts. This one is about tables, keys, and integrity that clients never see directly.

Will the AI just give me a generic schema?

Only if you let it. The point of the steps is to force the model onto your real entities, access patterns, and invariants — so the schema fits your domain instead of a textbook example. The modeling calls stay yours.

Part of these blueprints

Complete build journeys that include this workflow as a stage.

Blueprint Build a SaaS MVP with AI The full path from idea to a shipped SaaS MVP — define and scope the requirements, design the architecture, API, and data model, then build it reviewed, tested, secured, cost-controlled, and deployed. View Blueprint → Blueprint Build an API Backend with AI The full path to a backend you can put clients on — define the requirements, design the architecture, API contract, data model, and access control, then build it reviewed, tested, secured, and shipped. View Blueprint → Blueprint Build a Marketplace with AI The full path to a two-sided platform — define the buyer-and-seller requirements, model the data, design the API, build roles and permissions, wire integrations, design the UI, then test, secure, and ship it. View Blueprint → Blueprint Build a CRM with AI The full path to a CRM that fits your sales process — define the contacts, deals, and pipeline, model the data that ties them together, then build the roles, integrations, and pipeline UI, and ship. View Blueprint → Blueprint Build an E-commerce Store with AI The full path to a store you own end to end — model the catalog and orders, design the storefront and checkout, add customer accounts and payments, then secure it, test it, and ship. View Blueprint → Blueprint Build a Data Pipeline with AI The full path to a pipeline that moves data without corrupting it — design the ingestion and transforms, extract and structure the sources, gate the quality, store it, then deliver and ship it monitored. View Blueprint →

Where to go next

Recommended next workflow AI Auth & Identity Workflow Design access control before you build it, not after a breach — choose the authentication approach, model the roles and permissions, review the design for gaps, then document the identity model. Use when You're building access control and need authentication, roles, and permissions designed before you implement them. Start this workflow

Related workflows

Coding Workflows AI Project Architecture Workflow Design a system's architecture on its real trade-offs instead of a confident diagram — put the model in an architect's seat, work the decisions one at a time, and write down the why. Use when You're designing a new system or feature and need to decide and document the architecture before building it. View Playbook → Coding Workflows AI API Design Workflow Design an API on its contract instead of discovering it endpoint by endpoint — model the resources, design the endpoints and payloads, pin the contract, then review it before code locks it in. Use when You're about to build or extend an API and want the resources, endpoints, and contracts designed before implementation locks them in. View Playbook → Structured Output Workflows AI Data Pipeline & ETL Workflow Design a pipeline that moves data without corrupting it — map the sources and ingestion, design the transformation stages, set validation and quality gates, then document the pipeline and monitoring. Use when You're moving data between systems and need the ingestion, transforms, and quality checks designed before you build the pipeline. View Playbook →

Tip: Each step's resource opens its tool pre-filled — start at step one and carry the output forward.

All playbooks