Prompt

You are a database architect designing and optimizing a database solution for [APPLICATION/SYSTEM_NAME].

Create a comprehensive database design and optimization plan:

## Requirements Analysis

**Functional Requirements:**
- Data types and entities: [Core business objects, relationships, attributes]
- CRUD operations: [Create, read, update, delete patterns and frequency]
- Query patterns: [Common queries, reporting needs, analytics requirements]
- Transaction requirements: [ACID properties, isolation levels, consistency needs]
- Integration needs: [External systems, APIs, data feeds, ETL processes]

**Non-Functional Requirements:**
- Performance: [Response time, throughput, concurrent user targets]
- Scalability: [Growth projections, user volume, data volume increases]
- Availability: [Uptime requirements, disaster recovery, maintenance windows]
- Security: [Access controls, encryption, compliance requirements]
- Reliability: [Data integrity, backup requirements, error handling]

## Database Architecture Design

**Database Selection:**
- Database type: [Relational, NoSQL, NewSQL, time-series, graph]
- Technology choice: [PostgreSQL, MySQL, MongoDB, Cassandra, etc.]
- Selection criteria: [Performance, scalability, consistency, ecosystem, cost]
- Architecture pattern: [Single database, microservices, polyglot persistence]

**Schema Design (for Relational Databases):**
- Entity-Relationship Model: [Core entities, relationships, constraints]
- Table structures: [Primary keys, foreign keys, indexes, constraints]
- Normalization: [1NF, 2NF, 3NF analysis and denormalization decisions]
- Indexing strategy: [Primary indexes, secondary indexes, composite indexes]

**Data Modeling (for NoSQL Databases):**
- Document structure: [JSON/BSON document design for document databases]
- Key-value pairs: [Optimal key design and value structure]
- Column families: [Wide-column store organization]
- Graph modeling: [Nodes, edges, properties for graph databases]

## Performance Optimization

**Query Optimization:**
- Query analysis: [Execution plans, cost analysis, bottleneck identification]
- SQL optimization: [Query rewriting, join optimization, subquery elimination]
- Index optimization: [Index selection, covering indexes, query hints]
- Stored procedures: [Complex logic encapsulation, reduced network traffic]
- Caching strategies: [Query result caching, prepared statements]

**Database Tuning:**
- Configuration optimization: [Memory allocation, buffer pools, connection pools]
- Storage optimization: [File placement, disk I/O optimization, compression]
- Lock management: [Lock types, isolation levels, deadlock prevention]
- Statistics maintenance: [Automated statistics updates, histogram accuracy]
- Resource allocation: [CPU, memory, disk, network optimization]

**Scalability Architecture:**
- Horizontal scaling: [Read replicas, sharding, federation]
- Vertical scaling: [Hardware optimization, resource monitoring, capacity planning]
- Caching strategy: [Application-level caching, database caching, CDN caching]
- Connection management: [Connection pooling, load balancing, failover mechanisms]

## High Availability & Disaster Recovery

**High Availability Design:**
- Replication setup: [Master-slave, master-master, cluster replication]
- Failover mechanisms: [Automatic failover, manual failover procedures]
- Load balancing: [Application-level, proxy-level load distribution]
- Health monitoring: [Database health checks, performance monitoring]

**Disaster Recovery Planning:**
- Backup strategies: [Full, incremental, differential backups]
- Recovery objectives: [RTO (Recovery Time Objective), RPO (Recovery Point Objective)]
- Geographic redundancy: [Multi-region deployment, data synchronization]
- Recovery procedures: [Step-by-step recovery processes, testing protocols]

## Security Implementation

**Access Control:**
- Authentication: [User authentication, service accounts, certificate-based]
- Authorization: [Role-based access control, row-level security]
- Privilege management: [Principle of least privilege, privilege escalation]
- Audit trails: [Login monitoring, query logging, data access tracking]

**Data Protection:**
- Encryption at rest: [Transparent data encryption, column-level encryption]
- Encryption in transit: [SSL/TLS, VPN connections]
- Key management: [Encryption key rotation, secure key storage]
- Data masking: [Development environment data protection]

## Implementation Timeline & Success Metrics

**Phase 1: Design & Setup (Months 1-2)**
- Requirements finalization
- Architecture design completion
- Technology selection and procurement
- Development environment setup
- Initial schema design

**Phase 2: Development & Testing (Months 3-4)**
- Schema implementation
- Performance optimization
- Security implementation
- Testing and validation
- Documentation completion

**Phase 3: Deployment & Migration (Months 5-6)**
- Production environment setup
- Data migration execution
- Performance tuning
- Monitoring implementation
- User training and handoff

**Success Metrics:**
- Performance: [Query response time < Xms, throughput > Y ops/sec]
- Availability: [Uptime > 99.9%, RTO < X hours, RPO < Y minutes]
- Security: [Zero security incidents, compliance audit passing]
- Cost: [Stay within budget, optimize cost per transaction]
- Scalability: [Support X concurrent users, handle Y data growth]

Include specific implementation code, configuration examples, and measurable performance targets throughout the design.