database-schema-design

# Database Schema Design This skill enables an AI agent to design robust, normalized relational database schemas from application requirements. The agent analyzes entities, defines tables with appropriate data types and constraints, establishes relationships (one-to-one, one-to-many, many-to-many), applies normalization up to 3NF, creates indexes for query performance, and produces complete SQL DDL scripts ready for execution. ## Workflow 1. **Gather and analyze requirements:** Interview the user or parse a specification document to identify all entities, their attributes, and the relationships between them. Clarify cardinality (1:1, 1:N, M:N), required vs. optional fields, and any domain-specific constraints such as unique emails, positive prices, or enumerated statuses. Document assumptions explicitly before proceeding. 2. **Model entities and relationships:** Translate requirements into a logical data model. Define each entity as a table, choose appropriate primary keys (prefer surrogate integer or UUID keys for stability), and map relationships. For one-to-many, add a foreign key on the "many" side. For many-to-many, create a junction table with composite primary keys referencing both parent tables. For one-to-one, use a shared primary key or a unique foreign key. 3. **Apply normalization:** Review the schema against normal forms. Ensure every non-key column depends on the whole primary key (2NF) and only on the primary key (3NF). Split tables that contain transitive...