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.
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
Prompt Gemini for Creating a Software Architecture
Designing a solid software architecture is one of the most critical steps in any development project. A poor architecture leads to technical debt, scalability issues, and exploding maintenance costs. Gemini, Google's AI model, excels at structuring complex systems thanks to its ability to simultaneously analyze multiple technical constraints. By providing a well-built prompt, you get a detailed architecture covering technology choices, design patterns, data flows, and deployment strategies. Whether you are starting from scratch or looking to modernize an existing monolith, Gemini helps you explore trade-offs between microservices and monolith, identify potential points of failure, and document your architectural decisions. The tool is particularly useful for architects wanting to validate their intuitions, senior developers upskilling on architecture, and teams needing a structured starting point for their ADRs (Architecture Decision Records). Here is how to formulate your prompts to get the most out of it.
Generate Mocks and Fixtures for Your Automated Tests
A prompt to automatically generate realistic mocks, stubs and data fixtures adapted to your test framework and use cases.
Automatically Generate Unit Tests with AI
Automatically generate an exhaustive unit test suite covering nominal cases, edge cases, and error cases for any source code.
Create a Python Automation Script
Create a professional Python automation script with CLI configuration, structured logging, error handling, and tests.