Prompt Gemini 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, optimizes query performance, and facilitates long-term maintenance. Gemini, Google's AI model, excels at generating database schemas thanks to its deep understanding of relational modeling principles, normal forms, and integrity constraints. By providing a structured prompt describing your business domain, main entities, and use cases, Gemini can produce a complete schema with tables, typed columns, primary keys, foreign keys, indexes, and constraints. Whether you work with PostgreSQL, MySQL, SQLite, or any other DBMS, Gemini adapts the generated SQL syntax to the target dialect. This approach significantly accelerates the design phase while adhering to modeling best practices, allowing you to move more quickly to application development.
Paste in your AI
Paste this prompt in ChatGPT, Claude or Gemini and customize the variables in brackets.
You are a senior database architect specializing in relational modeling. Generate a complete database schema for an application of [DESCRIBE_YOUR_APPLICATION].
Business context:
- Domain: [e.g., e-commerce, project management, social network]
- Target users: [e.g., B2B companies, general public]
- Estimated volume: [e.g., 10,000 users, 1 million transactions/month]
Technical requirements:
- Target DBMS: [PostgreSQL / MySQL / SQLite]
- Include precise data types for each column
- Apply at least 3rd Normal Form (3NF)
- Add relevant NOT NULL, UNIQUE, CHECK constraints
- Define all primary and foreign keys with ON DELETE/ON UPDATE
- Propose necessary indexes for frequent queries
- Include audit columns (created_at, updated_at)
Expected output format:
- A textual diagram of relationships between entities
- A complete and executable SQL CREATE TABLE script
- A summary table of tables with their role
- Recommended indexes with justification
- SQL queries for the 5 most common operations
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 combines a precise expert role with explicit technical constraints that prevent Gemini from producing a generic or incomplete schema. Specifying the business context and data volume allows the model to tailor its choices of types and indexes. The structured output format with five deliverables forces a comprehensive and directly usable response.
Use Cases
Variants
Expected Output
Gemini produces a complete database schema including a textual entity-relationship diagram, ready-to-run SQL CREATE TABLE scripts with all types, constraints, and indexes, as well as an explanatory table for each table. You also get SQL queries for common operations, allowing you to immediately validate the schema's relevance for your real-world use cases.
Frequently Asked Questions
Can Gemini generate a schema for any database management system?
Yes, Gemini is proficient in the SQL syntax of most common DBMS: PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, and Oracle. Just specify the target DBMS in your prompt for Gemini to adapt data types (e.g., SERIAL vs AUTO_INCREMENT), native functions, and specific extensions. For NoSQL databases like MongoDB, Gemini can also generate collection schemas with JSON Schema validation.
How can I ensure the generated schema complies with normal forms?
Explicitly state the desired normalization level in your prompt (2NF, 3NF, BCNF). Also ask Gemini to justify its modeling choices and flag any intentional denormalization. You can add an instruction like "Verify that each table meets 3NF and explain any exceptions." This forces the model to analyze its own output and document the trade-offs between normalization and performance.
Can I provide an existing schema to Gemini for improvement or extension?
Absolutely. Paste your existing SQL script into the prompt and ask Gemini to analyze it, identify issues (unindexed columns, missing relationships, unsuitable types), and propose an improved version. This approach is especially useful for auditing a legacy database or adding new features to an existing schema while preserving compatibility with live data.
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
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.
Analyze and Optimize Algorithmic Complexity
Analyze the Big O complexity of your algorithms and optimize them with appropriate data structures and more efficient algorithms.