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.
Tu es un architecte de bases de données expérimenté. Conçois un schéma de base de données complet pour une application de type [TYPE_APPLICATION] utilisant [SYSTEME_SGBD]. Le domaine métier est : [DESCRIPTION_METIER]. Ton livrable doit inclure : 1. **Modèle conceptuel** : Liste toutes les entités principales, leurs attributs et les relations entre elles (1-1, 1-N, N-N). 2. **Script SQL de création** : Génère le DDL complet avec : - Tables avec colonnes typées correctement - Clés primaires (préfère les UUID ou SERIAL selon le SGBD) - Clés étrangères avec ON DELETE / ON UPDATE appropriés - Contraintes NOT NULL, UNIQUE, CHECK pertinentes - Index sur les colonnes fréquemment requêtées - Timestamps created_at et updated_at sur chaque table 3. **Normalisation** : Assure-toi que le schéma respecte au minimum la 3NF. Justifie toute dénormalisation volontaire. 4. **Diagramme textuel** : Fournis un diagramme ERD en notation Mermaid. 5. **Données de test** : Génère 3 à 5 INSERT par table avec des données réalistes. 6. **Recommandations** : Propose des optimisations (partitionnement, index partiels, vues matérialisées) adaptées au volume estimé de [VOLUME_DONNEES] enregistrements. Contraintes techniques supplémentaires : [CONTRAINTES_SPECIFIQUES]
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.
Learn more
Check the full skill on Prompt Guide to master this technique from A to Z.
View on Prompt Guide📬 Get new prompts every week
Join our newsletter and never miss a prompt.
Similar Prompts
Create Your First API with Express.js
Learn to create your first Express.js REST API from A to Z with pedagogical explanations adapted to beginners.
Generate Precise Regular Expressions with AI
This prompt generates precise and documented regular expressions, tailored to your programming language, with detailed explanations and built-in tests.
Implement Robust Error Handling in Node.js
Create a professional Node.js error handling architecture with custom error classes, centralized middleware, and monitoring.
Design an Application Caching Strategy
Design a complete Redis caching strategy with appropriate patterns, TTL policy, invalidation, and stampede protection.