Database Schema Designer
The Database Schema Designer transforms a plain-language description of your application's data requirements into a complete, normalized database schema with proper relationships, constraints, indexes, and migration-ready SQL. It handles the translation from "what the business needs" to "how the database should be structured" so you start with a solid foundation instead of refactoring later.
Backend developers, full-stack engineers, and technical leads use this template at the start of new projects, when adding major features that require new data models, or when restructuring a database that has grown organically without a clear design. It is especially valuable for developers who understand SQL but want a second opinion on normalization decisions, relationship modeling, and index strategy.
The prompt requires you to describe your data in business terms (entities, their attributes, and how they relate) rather than jumping straight to table definitions. This forces you to think about the domain model first, which consistently produces better schemas than translating a UI wireframe directly into tables. The output includes not just the CREATE TABLE statements but also the reasoning behind normalization choices and index selections, so you can evaluate trade-offs before committing to a structure.
This prompt is just the starting point
Score it with AI, optimize it with one click, track versions, and build your prompt library.
The Prompt
Design a database schema based on the following application requirements: **Database Engine**: [PostgreSQL / MySQL / SQL Server / SQLite] **Application Description**: [BRIEF DESCRIPTION OF WHAT THE APP DOES, e.g., "A project management tool where teams create projects, assign tasks to members, track time spent, and generate weekly reports"] **Core Entities and Their Attributes**: [LIST EACH ENTITY WITH ITS KEY ATTRIBUTES. For example: - User: name, email, role (admin/member/viewer), created date - Project: name, description, status (active/archived), owner (a user), deadline - Task: title, description, status (todo/in-progress/done), assignee (a user), project, priority (low/medium/high/urgent), due date, estimated hours - Time Entry: task, user, hours logged, date, notes] **Key Relationships and Rules**: [DESCRIBE BUSINESS RULES. For example: - A user can belong to multiple projects - Each task belongs to exactly one project - A task can have only one assignee at a time but reassignment history should be tracked - Deleting a project should not delete the time entries (they are needed for billing)] **Expected Scale**: [APPROXIMATE NUMBERS, e.g., "~500 users, ~200 active projects, ~10,000 tasks, ~50,000 time entries, growing 20% monthly"] **Special Requirements**: [ANY CONSTRAINTS, e.g., "Must support soft deletes", "Need full-text search on task titles", "Multi-tenant with row-level security", "Audit trail for all changes"] Provide your schema design in this structure: 1. **Entity-Relationship Summary**: A text description of all entities and their relationships (one-to-one, one-to-many, many-to-many), including join tables needed for many-to-many relationships. 2. **Normalization Notes**: Explain any normalization decisions. If you denormalized something for performance, explain why and what the trade-off is. 3. **Complete Schema SQL**: Provide the full CREATE TABLE statements with: - Primary keys (specify UUID vs auto-increment and why) - Foreign keys with appropriate ON DELETE / ON UPDATE actions - NOT NULL constraints where appropriate - CHECK constraints for enum-like columns - DEFAULT values where sensible - Timestamps (created_at, updated_at) with appropriate defaults 4. **Index Strategy**: For each index: - The CREATE INDEX statement - Which query pattern it supports - Whether it is a B-tree, hash, GIN, or GiST index (engine-specific) - Expected impact on write performance 5. **Migration Notes**: Any considerations for deploying this schema, including order of table creation (to satisfy foreign key dependencies) and seed data suggestions.
Usage Tips
- Describe entities in business terms, not table terms: Write "A user can belong to multiple projects" instead of "I need a user_projects junction table." Let the AI handle the translation so it can identify the optimal structure, which might differ from your initial assumption.
- Include expected scale from the start: A schema for 1,000 rows and a schema for 10 million rows may look very different. Scale information drives decisions about indexing, partitioning, and denormalization.
- Specify your database engine: PostgreSQL supports partial indexes, JSONB columns, and array types. MySQL handles enums differently. SQL Server has unique clustered index behavior. Engine-specific advice is significantly more useful.
- Review the normalization notes carefully: If the AI denormalized something (e.g., storing a computed count on a parent table), make sure you agree with the trade-off before implementing. You can ask follow-up questions about specific decisions.
- Extend with follow-up prompts: After getting the base schema, ask the AI to add audit logging, row-level security, soft delete support, or migration scripts for your ORM (Prisma, Drizzle, TypeORM, Alembic).
Get more from this prompt
Save it, score it with AI, optimize it, and track every version. Free to start.