prompt
FreeExpert database schema design prompt
FreeFree tier
About prompt
This is a system prompt designed for AI assistants acting as expert database architects with 15+ years of experience. It guides the AI through a structured five-step process to design normalized, production-ready relational database schemas: identifying entities and relationships, applying normalization (1NF–3NF/BCNF), defining table structures with constraints, designing index strategies (including composite and partial indexes), and providing migration notes. The prompt emphasizes practical trade-offs, proper foreign key indexing, and clear rationale for design decisions. Output is formatted as SQL DDL (PostgreSQL syntax) with explanatory notes and a text-based ERD.
Key Features
Structured 5-step schema design process (entities, normalization, tables, indexes, migration)
Covers normalization levels 1NF to BCNF with justified denormalization
Supports PostgreSQL, MySQL, and SQLite with PostgreSQL DDL default
Includes index strategy with composite and partial indexes
Provides migration notes with dependency-safe table creation order
Outputs text-based entity-relationship diagram and SQL CREATE TABLE statements
Pros & Cons
Pros
- Promotes best practices like proper normalization and foreign key indexing
- Encourages explicit rationale for design decisions
- Customizable with optional inputs (database engine, scale, multi-tenancy)
- Structured output includes both SQL and explanatory notes
- Suitable for both educational and production use
Cons
- Effectiveness depends on the capabilities of the AI model being used
- Requires user to provide clear domain description and access patterns
- Output is prompt-based; no direct integration with databases or ORMs
Best For
Designing relational schemas for SaaS applicationsE-commerce platform database designData-intensive system schema architectureGreenfield database design for new applicationsReviewing and refactoring existing schemas with normalization guidance
FAQ
What is the purpose of this prompt?
To guide an AI assistant in designing production-ready normalized relational database schemas with indexing and migration strategies.
What databases are supported?
The prompt is designed with PostgreSQL as default but mentions MySQL and SQLite; it outputs PostgreSQL syntax DDL.
What input does the user need to provide?
Required inputs: domain description, key entities and relationships, and primary access patterns. Optional inputs include database engine, scale, multi-tenancy, and existing schema.
Is this prompt part of a larger collection?
Yes, it is part of the 'ai-boost/awesome-prompts' repository on GitHub, which contains a curated list of prompts for various tasks.