AI Prompts Library

You are an expert database architect. Design a normalized, performant database schema based on the application requirements below.

**Application:** [DESCRIBE_YOUR_APPLICATION]
**Database Engine:** [PostgreSQL/MySQL/SQLite/MongoDB]
**Expected Scale:** [Number of users, records, transactions per day]
**Key Requirements:** [LIST_MAIN_FEATURES]

**Your schema design must include:**

1. **Entity-Relationship Diagram** (describe in text format):
   - All entities with their attributes and types
   - Primary keys, foreign keys, and unique constraints
   - Relationship types (1:1, 1:N, M:N) with cardinality
   - Junction tables for M:N relationships

2. **Table Definitions** (SQL CREATE statements):
   - Proper data types (VARCHAR lengths, DECIMAL precision, ENUM values)
   - NOT NULL constraints where appropriate
   - DEFAULT values (timestamps, UUIDs, boolean flags)
   - CHECK constraints for data validation
   - Proper indexing strategy (B-tree, GIN, GiST where appropriate)
   - Composite indexes for common query patterns
   - Partial indexes for filtered queries

3. **Normalization Analysis**:
   - Confirm the schema is at least in 3NF
   - Document any intentional denormalization with justification
   - Identify potential data anomalies and how they are prevented

4. **Performance Considerations**:
   - Index recommendations based on expected query patterns
   - Partitioning strategy if tables will exceed 10M rows
   - Materialized views for complex reporting queries
   - Connection pooling and caching recommendations

5. **Migration Script**: A versioned migration script (using a tool like Alembic, Flyway, or Prisma) that creates the schema from scratch.

6. **Seed Data**: Sample INSERT statements with realistic test data for development.

7. **Common Queries**: Write the 5 most common queries the application will need, with EXPLAIN analysis notes.

You are a database performance expert specializing in SQL query optimization. Analyze the provided query and suggest improvements for maximum performance.

**Query to Optimize:**
```sql
[PASTE YOUR SQL QUERY HERE]
```

**Database:** [PostgreSQL / MySQL / SQL Server / SQLite]
**Table Size:** [Approximate row counts for each table involved]
**Current Execution Time:** [If known]
**Available Indexes:** [List existing indexes, or say 'unknown']

**Optimization Analysis:**

1. **Query Plan Analysis:**
   - Identify sequential scans that should be index scans
   - Detect sort operations that could be avoided with indexes
   - Find hash joins that could be merge joins or nested loops
   - Spot unnecessary subqueries or CTEs

2. **Index Recommendations:**
   - Covering indexes for the most common queries
   - Composite indexes (correct column order for the query pattern)
   - Partial indexes for queries with WHERE filters
   - Expression indexes for computed conditions
   - Include CONCURRENTLY option for production index creation

3. **Query Rewriting:**
   - Replace correlated subqueries with JOINs
   - Convert IN (SELECT ...) to EXISTS where appropriate
   - Use window functions instead of self-joins
   - Replace DISTINCT with GROUP BY when more efficient
   - Optimize pagination (keyset pagination vs OFFSET)
   - Use UNION ALL instead of UNION when duplicates are impossible

4. **Schema Suggestions:**
   - Denormalization opportunities for read-heavy queries
   - Materialized views for complex aggregations
   - Partitioning strategy for large tables
   - Data type optimizations (e.g., INT vs BIGINT, VARCHAR vs TEXT)

5. **Optimized Query:**
   - Provide the rewritten query
   - Include EXPLAIN ANALYZE output comparison notes
   - Estimate the performance improvement

**Output**: The optimized query, required index DDL statements, and a brief explanation of each change and its expected impact.

Design database schema for [APPLICATION]:

**Naming Conventions**:
- Tables: plural, snake_case (users, order_items)
- Columns: singular, snake_case (user_id, created_at)
- Primary keys: id (integer, auto-increment)
- Foreign keys: [table_singular]_id

**Standard Columns** (all tables):
- id: Primary key
- created_at: Timestamp
- updated_at: Timestamp
- deleted_at: Soft deletes (optional)

**Relationships**:
- One-to-Many: Foreign key in child table
- Many-to-Many: Junction table
- One-to-One: Foreign key with UNIQUE constraint

**Indexes**:
- Primary key (automatic)
- Foreign keys
- Frequently queried columns
- Composite indexes for multi-column queries

**Data Types**:
- Use appropriate sizes (VARCHAR vs TEXT)
- ENUM for fixed options
- JSON for flexible data (use sparingly)
- UUID for distributed systems

**Constraints**:
- NOT NULL where appropriate
- UNIQUE for natural keys
- CHECK for validation
- DEFAULT values

**Normalization**:
- 3NF for OLTP
- Denormalize for read-heavy (with care)
- Avoid EAV anti-pattern

**Example Schema**:
```sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
```