Database Design

"Bad programmers worry about the code. Good programmers worry about data structures." — Linus Torvalds

TL;DR

Designs database schemas and data models across the three target platforms — relational (MySQL/MariaDB on Hostinger shared/VPS, Postgres/Neon on Vercel) and NoSQL document stores (Firestore on Firebase, MongoDB) — with indexing strategies, normalization/denormalization trade-offs, and query optimization. Use this skill when modeling a new data domain, migrating databases, or optimizing query performance. [EXPLICIT]

Platform Lens

Match each data domain to the platform that fits; do not assume a single vendor. [EXPLICIT]

Procedure

Step 1: Discover

• Identify entities, relationships, and cardinality from domain model
• Gather access patterns: what queries will the application run most frequently
• Determine consistency requirements: strong vs. eventual, ACID vs. BASE
• Assess data volume projections and growth rate

Step 2: Analyze

• Choose database paradigm based on access patterns and consistency needs
• For relational: normalize to 3NF, then selectively denormalize for read performance
• For Firestore: model data around queries (document = view), plan subcollections vs. root collections
• Design indexes to support all identified query patterns
• Plan for data lifecycle: archival, TTL, soft deletes

Step 3: Execute

• Create entity-relationship diagrams (ERD) or document structure diagrams
• Define schema with data types, constraints, and defaults
• Design composite indexes for multi-field queries
• Document Firestore security rules aligned to data model
• Write migration scripts or Firestore seeding patterns
• Plan backup and disaster recovery strategy

Step 4: Validate

• Verify every identified query pattern is supported by an index
• Confirm Firestore document sizes stay well under 1MB limit
• Check that write patterns won't create hot spots or contention
• Validate referential integrity strategy (foreign keys vs. application-level)

Quality Criteria

• All entity relationships are documented with cardinality
• Every frequent query has a supporting index
• Firestore models are designed query-first, not entity-first
• Data lifecycle (archival, deletion, retention) is planned
• Evidence tags applied to all claims

Anti-Patterns

• Relational thinking in Firestore (over-normalizing document databases)
• Missing indexes discovered only in production under load
• God collection: single Firestore collection with deeply nested documents

Related Skills

• domain-driven-design — domain model drives database entities
• api-design — API resources often map to database entities
• caching-strategy — caching compensates for database limitations

Usage

Example invocations:

• "/database-design" — Run the full database design workflow
• "database design on this project" — Apply to current context

Assumptions & Limits

• Assumes access to project artifacts (code, docs, configs) [EXPLICIT]
• Requires English-language output unless otherwise specified [EXPLICIT]
• Does not replace domain expert judgment for final decisions [EXPLICIT]

Edge Cases

Packet

Capas del packet, cargables bajo demanda (disciplina ICM: una capa por vez, nunca todas juntas): knowledge/ cuerpo de conocimiento · prompts/ prompts listos · examples/ salida de ejemplo · agents/ subagentes del packet · templates/ plantilla de output.