Prompt Claude for Generating a Database Schema
Designing a database schema is a fundamental step in any software project. A well-thought-out schema ensures data consistency, facilitates queries, and avoids long-term performance issues. Yet, this task requires expertise in relational modeling, normalization, and choosing data types suited to the target DBMS. Claude excels in this area: by providing a functional description of your project, it generates a complete schema with tables, columns, types, constraints, indexes, and relationships. It applies best practices for normalization, suggests consistent naming conventions, and anticipates common needs like audit columns or soft deletes. Whether you start from a specification, a list of business entities, or an existing schema to refactor, Claude produces a DDL ready to execute or a structured diagram that you can iteratively refine. This is a valuable accelerator for solo developers and teams alike who want to quickly validate a data architecture before coding.
Paste in your AI
Paste this prompt in ChatGPT, Claude or Gemini and customize the variables in brackets.
You are an experienced database architect. Based on the following functional description, generate a complete relational database schema.
Project: [DESCRIBE_YOUR_APPLICATION, e.g., project management platform with teams, tasks, and comments]
Target DBMS: [PostgreSQL / MySQL / SQLite]
Requirements:
- Apply at least 3rd Normal Form (3NF)
- Use snake_case naming conventions
- Add audit columns (created_at, updated_at) on every table
- Define primary keys, foreign keys, indexes, and NOT NULL / UNIQUE constraints
- Handle soft deletes (deleted_at) where relevant
- Add SQL comments on each table and key column
Output format:
- An entity-relationship diagram in text format (list of tables with their relationships)
- The complete DDL script (CREATE TABLE) ready to execute
- A separate script for recommended indexes
- Design considerations and trade-offs explained
Be exhaustive on data types (VARCHAR with length, TIMESTAMP WITH TIME ZONE, etc.) and justify your modeling choices.
Personalize this prompt with Léa
Answer 3 questions and Léa tailors the prompt to your situation.
Why this prompt works
This prompt works because it assigns Claude a precise expert role, activating its deep knowledge of relational modeling. It imposes concrete technical constraints (3NF, conventions, audit columns) that eliminate ambiguity and produce a directly usable result. Finally, the structured output format with four deliverables forces Claude to cover all aspects of a professional schema, from design to performance recommendations.
Use Cases
Variants
Expected Output
Claude generates a complete schema including a textual entity-relationship diagram, a DDL script with all tables, typed columns, primary and foreign keys, constraints, and SQL comments. You also get a separate index script optimized for common queries, and a section explaining modeling choices and trade-offs. Everything is ready to execute directly in your target DBMS.
Frequently Asked Questions
Can Claude generate a schema for any DBMS?
Yes, Claude masters the DDL syntax of all major DBMS: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, Oracle, and even NoSQL databases like MongoDB (with validation schemas). Always specify the target DBMS in your prompt because data types, constraints, and features vary significantly. For example, PostgreSQL supports JSONB types and GIN indexes, while MySQL has specific storage engine features (InnoDB vs MyISAM).
How do I ask Claude to modify an existing schema instead of creating a new one?
Paste your existing SQL schema into the prompt and explicitly ask for modifications. For example: "Here is my current schema [paste DDL]. I need to add a roles and permissions system. Generate only the necessary ALTER TABLE and CREATE TABLE statements, without touching the existing tables except to add foreign keys." Claude will generate incremental migration scripts rather than a full schema from scratch.
Does the schema generated by Claude adhere to normal forms?
Claude applies reasonable normalization by default, generally in 3NF. However, it is recommended to state this explicitly in your prompt, because without clear instruction, Claude might make denormalization compromises to simplify the response. If you have strict requirements (BCNF, 4NF), mention them. Claude is also able to justify when controlled denormalization is preferable for performance, for example to avoid costly joins on very frequent queries.
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
Ajoute dans la description fonctionnelle les cardinalités explicites (1-N, N-N) et les contraintes métier (ex: une tâche ne peut avoir qu'un seul assigné). Sans cela, le schéma reste générique et peut omettre des tables de jonction ou des clés étrangères cruciales.
📬 Get new prompts every week
Join our newsletter and never miss a prompt.