Schema design, entity relationships, normalization, and database patterns. Use when designing database schemas, modeling domain entities, deciding between normalized and denormalized structures, choosing between relational and NoSQL approaches, or planning schema migrations. Covers ER modeling, normal forms, and data evolution strategies.
/plugin marketplace add rsmdt/the-startup/plugin install team@the-startupThis skill inherits all available tools. When active, it can use any tool Claude has access to.
templates/schema-design-template.mdData models outlive applications. A well-designed schema encodes business rules, enforces integrity, and enables performance optimization. The goal is to create models that are correct first, then optimize for access patterns while maintaining data integrity.
Entities represent distinct business concepts that have identity and lifecycle.
Entity Identification Checklist:
Common Entity Patterns:
| Type | Notation | Example | Implementation |
|---|---|---|---|
| One-to-One | 1:1 | User - Profile | FK with unique constraint or same table |
| One-to-Many | 1:N | Customer - Orders | FK on the "many" side |
| Many-to-Many | M:N | Students - Courses | Junction/bridge table |
Relationship Considerations:
Attribute Types:
Key Types:
Best Practice: Prefer surrogate keys for primary keys; use natural keys as unique constraints.
Each normal form builds on the previous. Normalize until requirements dictate otherwise.
Rule: Eliminate repeating groups; each cell contains atomic values.
Violation Example:
Order(id, customer, items: "widget,gadget,thing")
Resolution:
Order(id, customer)
OrderItem(order_id, item_name)
Rule: Remove partial dependencies on composite keys.
Violation Example:
OrderItem(order_id, product_id, product_name, quantity)
^-- depends only on product_id
Resolution:
OrderItem(order_id, product_id, quantity)
Product(product_id, product_name)
Rule: Remove transitive dependencies; non-key columns depend only on the key.
Violation Example:
Employee(id, department_id, department_name)
^-- depends on department_id, not employee id
Resolution:
Employee(id, department_id)
Department(id, name)
Rule: Every determinant is a candidate key.
Violation Example:
CourseOffering(student, course, instructor)
-- Constraint: each instructor teaches only one course
-- instructor -> course (but instructor is not a candidate key)
Resolution:
InstructorCourse(instructor, course) -- instructor is key
Enrollment(student, instructor) -- references instructor
Stop at 3NF for most OLTP systems. Consider BCNF when:
Denormalize intentionally for read performance, not out of laziness.
Store derived values to avoid repeated computation.
Order
- subtotal (calculated once on item changes)
- tax_amount (calculated once)
- total (calculated once)
Trade-off: Faster reads, more complex writes, potential consistency issues.
Embed frequently-accessed related data.
Post
- author_id
- author_name (copied from User.name)
- author_avatar_url (copied from User.avatar_url)
Trade-off: Eliminates joins, requires synchronization on source changes.
Pre-compute summaries for reporting.
DailySales
- date
- product_id
- units_sold (sum)
- revenue (sum)
Trade-off: Fast analytics, storage overhead, stale until refreshed.
| Factor | Normalize | Denormalize |
|---|---|---|
| Write frequency | High | Low |
| Read frequency | Low | High |
| Data consistency | Critical | Eventual OK |
| Query complexity | Simple | Complex joins |
| Data size | Small | Large |
Embedding Pattern: Embed related data that is read together and has 1:few relationship.
{
"order_id": "123",
"customer": {
"id": "456",
"name": "Jane Doe",
"email": "jane@example.com"
},
"items": [
{"product_id": "A1", "name": "Widget", "quantity": 2}
]
}
Referencing Pattern: Reference related data when it changes independently or is shared.
{
"order_id": "123",
"customer_id": "456",
"item_ids": ["A1", "B2"]
}
Hybrid Pattern: Embed summary data, reference for full details.
{
"order_id": "123",
"customer_summary": {
"id": "456",
"name": "Jane Doe"
},
"items": [
{"product_id": "A1", "name": "Widget", "quantity": 2}
]
}
Access Pattern Design: Design keys around query patterns.
USER:{user_id} -> user data
USER:{user_id}:ORDERS -> list of order ids
ORDER:{order_id} -> order data
Composite Keys: Combine entity type with identifiers for namespacing.
Partition Key Design: Choose partition keys for even distribution and access locality.
Primary Key: (user_id, order_date)
^-- partition key (distribution)
^-- clustering column (ordering)
Avoid:
Node and Relationship Design:
(User)-[:PURCHASED {date, amount}]->(Product)
(User)-[:FOLLOWS]->(User)
(Product)-[:BELONGS_TO]->(Category)
Expand-Contract Pattern:
Blue-Green Schema:
Versioned Documents (NoSQL):
{
"_schema_version": 2,
"name": "Jane",
"email": "jane@example.com"
}
Handle multiple versions in application code during transition.
This skill should be used when the user asks to "create an agent", "add an agent", "write a subagent", "agent frontmatter", "when to use description", "agent examples", "agent tools", "agent colors", "autonomous agent", or needs guidance on agent structure, system prompts, triggering conditions, or agent development best practices for Claude Code plugins.
This skill should be used when the user asks to "create a slash command", "add a command", "write a custom command", "define command arguments", "use command frontmatter", "organize commands", "create command with file references", "interactive command", "use AskUserQuestion in command", or needs guidance on slash command structure, YAML frontmatter fields, dynamic arguments, bash execution in commands, user interaction patterns, or command development best practices for Claude Code.
This skill should be used when the user asks to "create a hook", "add a PreToolUse/PostToolUse/Stop hook", "validate tool use", "implement prompt-based hooks", "use ${CLAUDE_PLUGIN_ROOT}", "set up event-driven automation", "block dangerous commands", or mentions hook events (PreToolUse, PostToolUse, Stop, SubagentStop, SessionStart, SessionEnd, UserPromptSubmit, PreCompact, Notification). Provides comprehensive guidance for creating and implementing Claude Code plugin hooks with focus on advanced prompt-based hooks API.