Data Modeling
Designs database schemas with proper entity-relationship modeling for both relational (PostgreSQL, MySQL) and document (MongoDB) databases. Covers normalization forms (1NF through BCNF), strategic denormalization for read performance, polymorphic associations, self-referencing hierarchies, temporal data modeling, multi-tenant schemas, and indexing strategies aligned with query access patterns.
Usage
Describe your application domain, the entities and their relationships, expected data volumes, and primary access patterns (read-heavy, write-heavy, mixed). Specify your database engine and any specific requirements like soft deletes, audit trails, or multi-tenancy. The skill produces a complete schema design with table definitions, relationships, indexes, and constraints.
Examples
- "Design a schema for a project management app with users, organizations, projects, tasks, and comments"
- "Model a social media platform with users, posts, follows, likes, and a newsfeed query pattern"
- "Create a multi-tenant SaaS schema using row-level security with tenant_id partitioning"
- "Design a schema for an e-commerce platform handling products with variable attributes (size, color, material)"
Guidelines
- Start with 3NF normalization, then denormalize strategically based on measured query performance needs
- Define foreign key constraints for referential integrity and cascade rules appropriate to the relationship
- Use UUIDs for public-facing IDs and BIGSERIAL for internal primary keys to balance security and performance
- Add created_at and updated_at timestamps to every table for debugging and audit capabilities
- Design indexes based on actual query patterns: composite indexes for multi-column WHERE and ORDER BY clauses
- Use check constraints for data validation at the database level as the last line of defense
- Model hierarchical data with materialized paths or closure tables for efficient tree queries
- Consider partitioning for tables expected to exceed 100M rows, using range partitioning on date columns