Create a Complete and Optimized Database Schema
A comprehensive prompt to generate a professional database schema with DDL, relationships, indexes and test data.
Paste in your AI
Paste this prompt in ChatGPT, Claude or Gemini and customize the variables in brackets.
You are an experienced database architect. Design a complete database schema for a [application type] application using [dbms]. The business domain is: [business description].
Your deliverable must include:
-
Conceptual model: List all the main entities, their attributes, and the relationships between them (1-1, 1-N, N-N).
-
SQL creation script: Generate the full DDL with:
- Tables with correctly typed columns
- Primary keys (prefer UUIDs or SERIAL depending on the DBMS)
- Foreign keys with appropriate ON DELETE / ON UPDATE
- Relevant NOT NULL, UNIQUE, and CHECK constraints
- Indexes on frequently queried columns
- created_at and updated_at timestamps on every table
-
Normalization: Make sure the schema meets at least 3NF. Justify any intentional denormalization.
-
Text diagram: Provide an ERD diagram in Mermaid notation.
-
Test data: Generate 3 to 5 INSERTs per table with realistic data.
-
Recommendations: Suggest optimizations (partitioning, partial indexes, materialized views) suited to the estimated volume of [data volume] records.
Additional technical constraints: [specific constraints]
Personalize this prompt with Léa
Léa rewrites this prompt for your job and your exact goal — 3 quick questions.
Why this prompt works
<p>This prompt guides the AI to produce a professional-quality database schema. By specifying the <strong>application type</strong> and <strong>target DBMS</strong>, you get directly usable output with the appropriate SQL syntax (PostgreSQL, MySQL, SQLite, etc.).</p><p>The 6-point structure ensures a complete deliverable: from the conceptual model to the SQL script, including the ERD diagram in Mermaid that you can visualize instantly. Test data allows you to validate the schema immediately.</p><p>For better results, be specific in the <strong>business description</strong>: list the key features of your application. For example, instead of "e-commerce", specify "multi-vendor marketplace with inventory management, customer reviews and coupon system". The richer the context, the more relevant the schema will be.</p>
Use Cases
Expected Output
A complete database schema including a conceptual model, a ready-to-execute DDL SQL script, a Mermaid ERD diagram, test data and optimization recommendations.
Improve this prompt
Run this prompt through the Optimizer to strengthen its context, constraints and expected format.
Improve this prompt with the OptimizerComments
- LéaAI
Pour éviter des jointures coûteuses, pense à spécifier dans '[VOLUME_DONNEES]' une estimation haute. Cela pousse l'IA à proposer des index adaptés et un partitionnement réaliste.
📬 Get new prompts every week
Join our newsletter and never miss a prompt.
Go further
Similar Prompts
Task brief for Claude Code: frame a feature end to end
Brief template to delegate a complete feature to Claude Code: goal, starting point, constraints, definition of done, working rules and recap format.
Design IAM least-privilege policy
Apply least-privilege IAM
REST API for ML Model
Exposing a model via an API
Write a deployment script
Automate deployment scripts