P
💻DeveloppementIntermediateAll AIs

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

Design a new project database from scratchMigrate an existing schema to another DBMSPrepare a pedagogical exercise on relational modelingAudit and improve an existing schema by regenerating it cleanly

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

Glossary Terms

ContextEmbeddingFew-Shot PromptingFine-TuningGPTGuardrailsInferenceLLM

📬 Get new prompts every week

Join our newsletter and never miss a prompt.

Similar Prompts

💻DeveloppementIntermediateAll AIs

Debug Your Python Code with ChatGPT

A structured prompt to get a complete analysis of your Python bugs: identification, root cause explanation, commented fix, and prevention tips.

093
💻DeveloppementIntermediateAll AIs

Configure an Optimized Dockerfile for Your Application

This prompt generates a production-ready Dockerfile with multi-stage build, security best practices and cache optimization, tailored to your language and framework.

043
💻DeveloppementIntermediateChatGPT

Create an Optimized Production Dockerfile

Create a multi-stage Dockerfile optimized for production with maximum security, lightweight image, and best practices.

31139
💻DeveloppementAdvancedAll AIs

Analyze and Optimize Algorithmic Complexity

Analyze the Big O complexity of your algorithms and optimize them with appropriate data structures and more efficient algorithms.

40131