Database Schema Designer

Design optimized database schemas for any application with both SQL and NoSQL options.

**Application Type:** [E-COMMERCE / SOCIAL NETWORK / SAAS PLATFORM / CONTENT MANAGEMENT / FINANCIAL APP / HEALTHCARE]
**Data Volume Estimate:** [10K records / 1M records / 100M+ records]
**Query Patterns:** [READ-HEAVY / WRITE-HEAVY / BALANCED / ANALYTICS-HEAVY]

**Schema Design Deliverables:**

**PostgreSQL Option:**
- Table definitions with column names, types, constraints (NOT NULL, UNIQUE, CHECK)
- Foreign key relationships with cascade rules (ON DELETE CASCADE/SET NULL/RESTRICT)
- Index strategy: B-tree for equality/range queries, GIN for JSONB, partial indexes for filtered queries
- Composite index ordering rationale (most selective column first)
- Partitioning strategy for large tables (range by date, hash by ID)
- Default values and generated columns where appropriate
- Migration file structure (up/down migrations with rollback safety)

**MongoDB Option:**
- Collection design with embedded vs. referenced document decisions
- Schema validation rules (JSON schema validation in MongoDB)
- Index strategy: single field, compound, text search, geospatial, TTL for expiring data
- Aggregation pipeline examples for common queries
- Sharding key selection rationale

**Cross-Cutting Concerns:**
- Data integrity constraints and trigger logic
- Soft delete pattern implementation (deleted_at column)
- Audit trail design (created_by, updated_by, timestamps)
- Backup strategy recommendations based on data criticality

How to Customize It

Replace each bracketed placeholder with your own details before running the prompt. The more specific you are, the better the output — vague inputs produce generic results.

• [E-COMMERCE / SOCIAL NETWORK / SAAS PLATFORM / CONTENT MANAGEMENT / FINANCIAL APP / HEALTHCARE]Swap in your e-commerce or social network or saas platform or content management or financial app or healthcare.
• [READ-HEAVY / WRITE-HEAVY / BALANCED / ANALYTICS-HEAVY]Swap in your read-heavy or write-heavy or balanced or analytics-heavy.

When to Use It

• Modeling a new application's core schema before the first migration is written, when changes are still free.
• Diagnosing a slow app whose schema grew organically — compare the generated design against what you have.
• Deciding between Postgres and a document store for a specific workload with your real query patterns on the table.

Tips for Better Results

• 1The [READ-HEAVY / WRITE-HEAVY] field changes indexing strategy fundamentally — measure your real ratio if you can, don't guess.
• 2List your five most frequent queries in the prompt; schemas should be designed backward from queries, and this forces that.
• 3Ask "what query will this schema make painful?" — every schema pessimizes something, and you want to choose what.