Create entity-relationship diagrams with proper normalization, keys, and cardinality for logical data models.
Generates normalized entity-relationship diagrams with proper keys and cardinality. Use when designing logical data models or converting requirements into database schemas.
/plugin marketplace add melodic-software/claude-code-plugins/plugin install data-architecture@melodic-softwareThis skill is limited to using the following tools:
Use this skill when:
Entity-Relationship (ER) modeling creates logical data models that define entities, attributes, relationships, and constraints independent of specific database implementation.
Symbols:
──┤├── One (mandatory)
──○── Zero (optional)
──< Many
──> Many (reverse)
Examples:
CUSTOMER ──┤├──○< ORDER (One customer, zero or more orders)
ORDER ──┤├──┤< ITEM (One order, one or more items)
PRODUCT >○──┤├── CATEGORY (Many products, one category)
┌──────────┐ ┌──────────┐
│ CUSTOMER │────<places>────│ ORDER │
└──────────┘ 1:N └──────────┘
Rule: Eliminate repeating groups; each column contains atomic values.
BEFORE (violates 1NF):
┌────────────────────────────────────────┐
│ Order │
├────────────────────────────────────────┤
│ order_id │ products (comma-separated) │
│ 1 │ "Laptop, Mouse, Keyboard" │
└────────────────────────────────────────┘
AFTER (1NF):
┌─────────────────────────┐ ┌──────────────────────────┐
│ Order │ │ OrderItem │
├─────────────────────────┤ ├──────────────────────────┤
│ order_id PK │───│ order_id FK │
│ order_date │ │ product_id FK │
│ customer_id FK │ │ quantity │
└─────────────────────────┘ └──────────────────────────┘
Rule: Be in 1NF + no partial dependencies (all non-key columns depend on the entire primary key).
BEFORE (violates 2NF):
┌───────────────────────────────────────────────────────┐
│ OrderItem │
├───────────────────────────────────────────────────────┤
│ order_id PK │ product_id PK │ product_name │ quantity │
│ (product_name depends only on product_id, not full key)│
└───────────────────────────────────────────────────────┘
AFTER (2NF):
┌─────────────────────────────┐ ┌──────────────────────┐
│ OrderItem │ │ Product │
├─────────────────────────────┤ ├──────────────────────┤
│ order_id PK FK │ │ product_id PK │
│ product_id PK FK ───────────┼───│ product_name │
│ quantity │ │ price │
└─────────────────────────────┘ └──────────────────────┘
Rule: Be in 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns).
BEFORE (violates 3NF):
┌────────────────────────────────────────────────────────┐
│ Order │
├────────────────────────────────────────────────────────┤
│ order_id PK │ customer_id │ customer_name │ order_date │
│ (customer_name depends on customer_id, not order_id) │
└────────────────────────────────────────────────────────┘
AFTER (3NF):
┌────────────────────────────┐ ┌─────────────────────────┐
│ Order │ │ Customer │
├────────────────────────────┤ ├─────────────────────────┤
│ order_id PK │ │ customer_id PK │
│ customer_id FK ────────────┼───│ customer_name │
│ order_date │ │ email │
└────────────────────────────┘ └─────────────────────────┘
Rule: Be in 3NF + every determinant is a candidate key.
BEFORE (violates BCNF):
┌───────────────────────────────────────────────────────────┐
│ CourseInstructor │
├───────────────────────────────────────────────────────────┤
│ student_id PK │ course PK │ instructor │
│ (instructor → course, but instructor is not a candidate key) │
└───────────────────────────────────────────────────────────┘
AFTER (BCNF):
┌──────────────────────────┐ ┌────────────────────────────┐
│ Enrollment │ │ CourseAssignment │
├──────────────────────────┤ ├────────────────────────────┤
│ student_id PK FK │ │ instructor PK │
│ instructor_id PK FK ─────┼───│ course │
└──────────────────────────┘ └────────────────────────────┘
# Logical Data Model: [System Name]
## 1. Entity Definitions
### Entity: Customer
| Attribute | Type | Constraints |
|-----------|------|-------------|
| customer_id | UUID | PK |
| email | VARCHAR(255) | UK, NOT NULL |
| name | VARCHAR(100) | NOT NULL |
| created_at | TIMESTAMP | NOT NULL, DEFAULT NOW |
| status | ENUM | NOT NULL, DEFAULT 'active' |
**Business Rules:**
- Email must be unique across all customers
- Status can be: active, suspended, closed
### Entity: Order
| Attribute | Type | Constraints |
|-----------|------|-------------|
| order_id | UUID | PK |
| customer_id | UUID | FK → Customer, NOT NULL |
| order_date | DATE | NOT NULL |
| status | ENUM | NOT NULL |
| total_amount | DECIMAL(10,2) | NOT NULL |
**Business Rules:**
- Order must have at least one OrderItem
- Total is calculated from items
## 2. Relationship Definitions
| Relationship | From | To | Cardinality | Participation |
|--------------|------|-----|-------------|---------------|
| places | Customer | Order | 1:N | Optional |
| contains | Order | OrderItem | 1:N | Mandatory |
| references | OrderItem | Product | N:1 | Mandatory |
## 3. Entity-Relationship Diagram
```mermaid
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_ITEM : contains
PRODUCT ||--o{ ORDER_ITEM : "appears in"
CATEGORY ||--|{ PRODUCT : categorizes
CUSTOMER {
uuid customer_id PK
varchar email UK
varchar name
timestamp created_at
enum status
}
ORDER {
uuid order_id PK
uuid customer_id FK
date order_date
enum status
decimal total_amount
}
ORDER_ITEM {
uuid item_id PK
uuid order_id FK
uuid product_id FK
int quantity
decimal unit_price
}
PRODUCT {
uuid product_id PK
uuid category_id FK
varchar sku UK
varchar name
decimal price
}
CATEGORY {
uuid category_id PK
varchar name UK
uuid parent_id FK
}
| Entity | Primary Key | Unique Keys | Foreign Keys |
|---|---|---|---|
| Customer | customer_id | - | |
| Order | order_id | - | customer_id |
| OrderItem | item_id | - | order_id, product_id |
| Product | product_id | sku | category_id |
| Category | category_id | name | parent_id (self) |
| Key Type | Purpose | Example |
|---|---|---|
| Primary Key (PK) | Unique row identifier | order_id |
| Unique Key (UK) | Alternative unique identifier | email, sku |
| Foreign Key (FK) | Reference to another table | customer_id |
| Composite Key | Multi-column primary key | (order_id, product_id) |
| Natural Key | Business-meaningful identifier | social_security_number |
| Surrogate Key | System-generated identifier | auto-increment, UUID |
Crow's Foot:
┤├ One (and only one)
○ Zero (optional)
< Many
Combinations:
──┤├──┤├── One-to-One (mandatory both)
──┤├──○── One-to-One (optional one side)
──┤├──○<── One-to-Many (mandatory one, optional many)
──┤├──┤<── One-to-Many (mandatory both)
──>○──○<── Many-to-Many (optional both)
// Entity Configuration
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
public void Configure(EntityTypeBuilder<Customer> builder)
{
builder.ToTable("Customers");
// Primary Key
builder.HasKey(c => c.Id);
// Properties
builder.Property(c => c.Email)
.IsRequired()
.HasMaxLength(255);
builder.HasIndex(c => c.Email)
.IsUnique();
// Relationships
builder.HasMany(c => c.Orders)
.WithOne(o => o.Customer)
.HasForeignKey(o => o.CustomerId)
.OnDelete(DeleteBehavior.Restrict);
}
}
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
public void Configure(EntityTypeBuilder<Order> builder)
{
builder.ToTable("Orders");
builder.HasKey(o => o.Id);
// Owned entity (value object)
builder.OwnsOne(o => o.ShippingAddress, sa =>
{
sa.Property(a => a.Street).HasColumnName("ShippingStreet");
sa.Property(a => a.City).HasColumnName("ShippingCity");
});
// One-to-Many
builder.HasMany(o => o.Items)
.WithOne()
.HasForeignKey(i => i.OrderId)
.OnDelete(DeleteBehavior.Cascade);
}
}
| Scenario | Action |
|---|---|
| Reporting performance | Create read-optimized views |
| Frequent joins hurt performance | Consider caching or denormalization |
| Audit history | Keep normalized, add temporal tables |
| High write frequency | Stay normalized |
Option 1: Junction Table (Recommended)
PRODUCT >──○ PRODUCT_CATEGORY ○──< CATEGORY
Option 2: Array/JSON (for simple cases, NoSQL)
Product { categories: ["electronics", "accessories"] }
Inputs from:
conceptual-modeling skill → Entity candidatesOutputs to:
schema-design skill → Physical implementationThis 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 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 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.