Data Modeling Standards
Overview
This skill defines data modeling standards for consistent, performant, and maintainable database schemas. Good data models prevent data integrity issues, enable efficient queries, and make future changes manageable.
When to Use
- When designing new database tables or collections
- When modifying existing schemas (adding/removing columns)
- When reviewing data architecture decisions
- When choosing between SQL and NoSQL for new data
- When designing data migrations
- Don't use when: Working with temporary or throwaway data with no persistence needs
Core Procedures
Step 1: Design Principles
Apply these to all data models:
- Normalized: Eliminate redundancy (3NF minimum unless justified)
- Named Clearly: Tables as nouns (plural), columns as attributes (singular)
- Typed Strictly: Use specific types (not generic text/blob where possible)
- Constrained: Define NOT NULL, UNIQUE, FOREIGN KEY where applicable
- Indexed Strategically: Index columns used in WHERE, JOIN, ORDER BY
Step 2: Naming Conventions
| Element |
Convention |
Example |
| Tables |
lowercase_plural_nouns |
users, orders, order_items |
| Columns |
lowercase_snake_case |
first_name, created_at |
| Primary Keys |
id (or {table}_id) |
id, user_id |
| Foreign Keys |
{referenced_table}_id |
company_id, agent_id |
| Indexes |
idx_{table}_{columns} |
idx_users_email |
| Constraints |
fk/pk/ck/uk_{table}_{columns} |
fk_orders_user_id |
Step 3: Column Standards
Every table should have:
id UUID PRIMARY KEY -- or SERIAL/bigint
created_at TIMESTAMP NOT NULL DEFAULT NOW()
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
Additional standards:
- Use
BOOLEAN for true/false (not integers)
- Use
TEXT for unlimited strings, VARCHAR(n) for fixed max
- Use
DECIMAL for financial amounts (not FLOAT)
- Use
JSONB for flexible/semi-structured data (PostgreSQL)
- Use
ENUM or lookup tables for fixed value sets
- Always store timestamps in UTC
Step 4: Relationship Design
- One-to-Many: Foreign key on the "many" side
- Many-to-Many: Junction table with two foreign keys, composite primary key
- Self-Referencing: Foreign key to same table (e.g., manager_id → users.id)
- Always define ON DELETE behavior (CASCADE, SET NULL, RESTRICT)
Step 5: Migration Practices
- Always write forward migrations (no backward-only)
- Each migration is atomic and reversible (with down migration)
- Test migrations on copy of production data
- Never drop columns in same migration as adding replacement
- Data migrations separate from schema migrations
- Lock strategy for concurrent deployments
Quality Checklist
Error Handling
- Error: Migration fails on production
Response: Rollback immediately using down migration, diagnose in staging
- Error: Schema change causes data loss
Response: Write data migration first, preserve data in new structure, verify before dropping old
Cross-Team Integration
Related Skills: database-schema-management, api-design-standards, secrets-handling, regression-prevention
Used By: Database agents, backend engineers, data engineers, infrastructure teams
1---2name: data-modeling-standards3description: Use when designing, creating, or modifying database schemas, data structures, or data models. This skill provides standardized data modeling practices ensuring consistency, integrity, and maintainability across all data stores.4---56# Data Modeling Standards78## Overview9This skill defines data modeling standards for consistent, performant, and maintainable database schemas. Good data models prevent data integrity issues, enable efficient queries, and make future changes manageable.1011## When to Use12- When designing new database tables or collections13- When modifying existing schemas (adding/removing columns)14- When reviewing data architecture decisions15- When choosing between SQL and NoSQL for new data16- When designing data migrations17- **Don't use when:** Working with temporary or throwaway data with no persistence needs1819## Core Procedures2021### Step 1: Design Principles22Apply these to all data models:23- **Normalized:** Eliminate redundancy (3NF minimum unless justified)24- **Named Clearly:** Tables as nouns (plural), columns as attributes (singular)25- **Typed Strictly:** Use specific types (not generic text/blob where possible)26- **Constrained:** Define NOT NULL, UNIQUE, FOREIGN KEY where applicable27- **Indexed Strategically:** Index columns used in WHERE, JOIN, ORDER BY2829### Step 2: Naming Conventions30| Element | Convention | Example |31|---------|------------|---------|32| Tables | lowercase_plural_nouns | `users`, `orders`, `order_items` |33| Columns | lowercase_snake_case | `first_name`, `created_at` |34| Primary Keys | `id` (or `{table}_id`) | `id`, `user_id` |35| Foreign Keys | `{referenced_table}_id` | `company_id`, `agent_id` |36| Indexes | `idx_{table}_{columns}` | `idx_users_email` |37| Constraints | `fk/pk/ck/uk_{table}_{columns}` | `fk_orders_user_id` |3839### Step 3: Column Standards40Every table should have:41```sql42id UUID PRIMARY KEY -- or SERIAL/bigint43created_at TIMESTAMP NOT NULL DEFAULT NOW()44updated_at TIMESTAMP NOT NULL DEFAULT NOW()45```4647Additional standards:48- Use `BOOLEAN` for true/false (not integers)49- Use `TEXT` for unlimited strings, `VARCHAR(n)` for fixed max50- Use `DECIMAL` for financial amounts (not FLOAT)51- Use `JSONB` for flexible/semi-structured data (PostgreSQL)52- Use `ENUM` or lookup tables for fixed value sets53- Always store timestamps in UTC5455### Step 4: Relationship Design56- **One-to-Many:** Foreign key on the "many" side57- **Many-to-Many:** Junction table with two foreign keys, composite primary key58- **Self-Referencing:** Foreign key to same table (e.g., manager_id → users.id)59- Always define ON DELETE behavior (CASCADE, SET NULL, RESTRICT)6061### Step 5: Migration Practices62- Always write forward migrations (no backward-only)63- Each migration is atomic and reversible (with down migration)64- Test migrations on copy of production data65- Never drop columns in same migration as adding replacement66- Data migrations separate from schema migrations67- Lock strategy for concurrent deployments6869## Quality Checklist70- [ ] Tables follow naming conventions71- [ ] All tables have id, created_at, updated_at72- [ ] Foreign keys defined with ON DELETE behavior73- [ ] Indexes exist on frequently queried columns74- [ ] Data types are specific and appropriate75- [ ] Constraints prevent invalid data states76- [ ] Migration is reversible and tested77- [ ] No data loss in schema changes7879## Error Handling80- **Error:** Migration fails on production81 **Response:** Rollback immediately using down migration, diagnose in staging82- **Error:** Schema change causes data loss83 **Response:** Write data migration first, preserve data in new structure, verify before dropping old8485## Cross-Team Integration86**Related Skills:** database-schema-management, api-design-standards, secrets-handling, regression-prevention87**Used By:** Database agents, backend engineers, data engineers, infrastructure teams
Construct-AI-primary