Prompt Engineering

Schema Design & ERD Prompt — Entities, Relationships, and Keys

Turn product requirements and key entities into an ERD-ready schema spec: tables, fields, primary and foreign keys, relationships with cardinality, indexes, and constraints — with the modeling gaps named as open questions.

Open in Markdown Output Builder

Overview

Schema design fails when structure runs ahead of requirements — tables nobody needed, many-to-many relationships discovered in production, foreign keys with no decided delete behavior. This prompt works the other way: it takes the product requirements, the known entities and workflows, and the constraints, and produces an ERD-ready textual specification — a domain model, an entity/table list, fields and types, relationships with explicit cardinality, primary and foreign keys with their on-delete behavior, access-pattern-driven indexes, constraints, data-lifecycle and privacy notes, and a diagram-ready relationship summary. It normalizes to 3NF by default and justifies any denormalization, traces every table and key back to a requirement, and refuses to guess where the requirements are silent — the unresolved modeling questions are named as open questions, not quietly decided. It is a design spec to review and turn into migrations, not a set of raw migrations and not a visual diagram.

How to use this resource

Anchor to the requirements

Entities, keys, and relationships trace to stated requirements — the gaps become open questions, not guesses.
Model relationships explicitly

Every association gets a direction and a cardinality, and every many-to-many resolves to an explicit join table.
Make it ERD-ready

A diagram-ready relationship summary plus migration cautions, so the spec turns into tables and an ERD without rework.

Why This Works

Requirement tracing keeps the schema honest — no speculative tables that complicate every future migration
Explicit cardinality and join-table resolution prevent the many-to-many that gets discovered in production
Naming open questions instead of guessing surfaces the modeling decisions that are expensive to reverse, while they are still cheap

Best for

Designing a new database schema from product requirements
Turning entities and workflows into tables, keys, and relationships
Preparing an ERD-ready data model before writing migrations
Reviewing whether a data model has the right tables, keys, and relationships

Not for

Optimizing an existing slow SQL query — that's the SQL Optimization Prompt
Finding which indexes a workload is missing — that's Missing Index Analysis
Generating production migrations directly — design the schema here first, then write migrations
Drawing a visual ERD — this produces an ERD-ready textual model, not a diagram

Use cases

Designing the data model for a new SaaS product from its requirements
Turning a CRM's contacts, accounts, deals, and pipeline into a relational schema
Preparing an ERD-ready spec a team can review before any migration is written

FAQ

Will this schema prompt output an actual ERD diagram or migration files?

Neither — it produces an ERD-ready textual specification. The instruction is "Do not draw a visual diagram; describe the relationships precisely enough that someone else could," and section 10 gives a diagram-ready relationship summary like "User 1—N Order." The notFor line sends production migrations elsewhere. It's a markdown design spec to review, then hand to whoever writes the migrations or draws the diagram.

How does this prompt handle a many-to-many relationship between entities?

It refuses to leave one implicit — section 4 requires "Resolve every many-to-many into an explicit join table," written as A —(N:M)— B with direction and meaning, e.g. "Order N—M Product via OrderItem." It normalizes to 3NF by default and justifies any denormalization. The prompt generates the spec; your team reviews the modeling before migrating.

What does this prompt do when my product requirements don't decide something?

It names the gap instead of guessing. The rule is "Where the requirements don't decide a modeling question, put it in Open Questions — don't quietly pick one," and section 11 collects every assumption and unresolved gap with the question that would settle each. So a foreign key with no decided on-delete behavior surfaces as an open question. You resolve those before turning the spec into migrations.

Customize This Resource

Opens this setup in Markdown Output Builder. Generate to get the full markdown prompt — then adjust the sections, table rules, and consistency level.

Open in Markdown Output Builder

Inputs you need

Have these details ready before you copy or open the template.

The product requirements, features, or user stories
The key entities and the relationships between them
The core workflows or operations the system performs
Database type, expected scale, or compliance constraints
Any integrations or reporting and analytics needs

Prompt Template

Copy it as-is, or use Open in Markdown Output Builder to load it pre-filled and customize it with your own context.

DATABASE SCHEMA DESIGN

You are designing a database schema for this product. Work only from the evidence provided — do not invent requirements, entities, or constraints that aren't given. Where something essential is missing, name it as an open question instead of guessing.

PRODUCT REQUIREMENTS
[Paste the product requirements, features, or user stories here]

KNOWN ENTITIES & WORKFLOWS
[Paste the key entities, user flows, or operations the system performs here]

CONSTRAINTS
[Paste the database choice, expected scale, privacy/compliance needs, reporting/analytics needs, and integration constraints here — write "not specified" for any you don't have]

Produce an ERD-ready schema specification — a textual model an engineer can turn into migrations and a diagram. Do not draw a visual diagram; describe the relationships precisely enough that someone else could.

Return these sections:

1. DOMAIN MODEL SUMMARY
   - The core concepts and how they relate, in plain language, before any tables.

2. ENTITY / TABLE LIST
   - Every table (or collection), one line each, with its single responsibility.

3. FIELDS AND TYPES
   - Per table: each field, its type, nullability, default, and a one-line meaning. Mark derived or denormalized fields as such.

4. RELATIONSHIPS AND CARDINALITY
   - Every relationship as A —(1:1 / 1:N / N:M)— B, with direction and meaning. Resolve every many-to-many into an explicit join table.

5. PRIMARY KEYS AND FOREIGN KEYS
   - Each table's primary key (surrogate vs natural, with the reason). Every foreign key, its target, and its on-delete / on-update behavior.

6. INDEX RECOMMENDATIONS
   - Indexes that follow from the access patterns the requirements imply — each tied to the query or lookup it serves. Do not over-index; state the write cost of any non-trivial index.

7. CONSTRAINTS AND VALIDATION RULES
   - Uniqueness, check constraints, required field combinations, and any invariant the data must always satisfy.

8. DATA LIFECYCLE / RETENTION
   - What is created, updated, soft- vs hard-deleted, archived, or retained — plus any audit or history needs.

9. SECURITY AND PRIVACY NOTES
   - Fields carrying PII or sensitive data, where encryption-at-rest or access scoping matters, and any tenant-isolation rule.

10. ERD-READY RELATIONSHIP SUMMARY
    - A compact, diagram-ready list of every entity and its edges (e.g. "User 1—N Order", "Order N—M Product via OrderItem") — enough to draw the ERD without re-reading the spec.

11. OPEN QUESTIONS / ASSUMPTIONS
    - Every assumption made, marked as such, and every requirement gap that blocks a firm decision — each with the question that would resolve it.

12. MIGRATION / IMPLEMENTATION CAUTIONS
    - The order to create tables in, the relationships that are expensive to change later, and anything that should be decided now because it is costly to reverse.

Rules:
- Trace every table, key, and relationship back to a requirement or a stated entity — no speculative structure.
- Normalize to 3NF by default; call out and justify any deliberate denormalization.
- Stay database-agnostic unless a platform was named in the constraints; mark any platform-specific choice as such.
- Where the requirements don't decide a modeling question, put it in Open Questions — don't quietly pick one.

More resources from Markdown Output Builder

Resource

Markdown Output Prompt — Lock the Document Structure

The contract that stops AI documents from restructuring themselves: a pinned section skeleton, forced tables, and strict consistency rules.

Prompt Engineering

Resource

README Prompt Template

Overview, Installation, Usage, Examples, Configuration — the README skeleton with required, runnable code examples.

Engineering

Resource

API Documentation Prompt

Overview, Authentication, Endpoints, Error Handling, Rate Limits — endpoint docs in an identical structure, with parameter tables and runnable examples forced.

Engineering

Resources that pair well

Resource

Executive Summary Prompt

Summary, Why It Matters, What Happens Next — the executive summary contract for readers who will never open the source.

Operations

Resource

Force JSON Output from AI

Stop getting 'Sure, here is the JSON…' — the output-contract pattern that forces models to return only parseable JSON: schema, example, and a strict rule block.

Prompt Engineering

Resource

Fix Invalid JSON from AI

The JSON won't parse and you can't see why. Deterministic cause-sniffing — trailing commas, single quotes, unclosed brackets — and the repair prompt that fixes it.

Engineering

Related tools

Tool

Markdown Output Builder

Build prompts that produce documents in a fixed structure — headings, sections, and tables.

Structured Output

Projects that use this resource

Project

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.

11 stages Product Build

Project

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.

9 stages Software Development

Project

Build an Applicant Tracking System with AI

The full path to an applicant tracking system — model jobs, candidates, and hiring stages, generate job descriptions and screening prompts, parse résumés into structured data, design the hiring API, set roles, review security, then ship.

8 stages Business Systems

Project

Build a Booking System with AI

The full path to a booking and reservation system — model resources, availability, and reservations, design the booking API, set customer accounts, wire calendar and notification integrations, design the UI, review security, then ship.

8 stages Business Systems

Project

Build an Inventory Management System with AI

The full path to an inventory management system — model products, stock, locations, and movements, design the inventory API, set roles, plan supplier and POS integrations, design the UI, review data-integrity risks, then ship.

8 stages Business Systems

Project

Build an Internal Admin Panel with AI

The full path to an internal admin panel — model the operational data it manages, design the management API, define RBAC and admin permissions, design the tables and forms, review risky admin actions, then ship to trusted internal users.

7 stages Business Systems

Project

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.

10 stages Software Development

Project

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.

9 stages Business Systems

Project

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.

10 stages Commerce

Project

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.

6 stages Data Systems

Tip: Save time by exploring related resources and tools that integrate with this resource.