normalization-patterns

# Normalization Patterns — Normalization/Denormalization Pattern Library

Normalization rules, denormalization strategies, and domain-specific ERD patterns used by the data-modeler agent during data modeling.

## Target Agent

`data-modeler` — Directly applies the normalization rules and ERD patterns from this skill to data model designs.

## Normalization Stage Identification & Transformation

### 1NF (First Normal Form)
**Rule**: Every column must contain atomic (indivisible) values.

| Violation Pattern | Problem | Solution |
|------------------|---------|----------|
| Multi-value column | `tags = "java,python,go"` | Separate table (M:N) |
| Repeating groups | `phone1, phone2, phone3` | Separate table (1:N) |
| Composite values | `address = "123 Main St, City, State"` | Split into street/city/state columns |

### 2NF (Second Normal Form)
**Prerequisite**: Satisfies 1NF
**Rule**: Remove partial functional dependencies — separate columns that depend on only part of a composite primary key.

| Violation Example | Dependency | Solution |
|------------------|------------|----------|
| `order_details(order_id, product_id, product_name, quantity)` | product_name depends only on product_id | Separate into products table |

### 3NF (Third Normal Form)
**Prerequisite**: Satisfies 2NF
**Rule**: Remove transitive functional dependencies — a non-key column must not determine another non-key column.

| Violation Example | Dependency | Solution |
|------------------|------------|----------|
| `employees(id, dept_id, dept_name, dept_head)` | dept_name, dept_head transitively depend on dept_id | Separate into departments table |

### BCNF (Boyce-Codd Normal Form)
**Rule**: Every determinant must be a candidate key.

| Violation Example | Problem | Solution |
|------------------|---------|----------|
| `enrollment(student, course, professor)` where professor -> course | Professor is a determinant but not a candidate key | Separate into professor-course table |

## Normalization Decision Flowchart

```
Data Analysis
  ├─ Atomic value violation? -> 1NF transformation
  ├─ Composite key & partial dependency? -> 2NF transformation
  ├─ Transitive dependency? -> 3NF transformation
  ├─ Non-candidate-key determinant? -> BCNF transformation
  └─ Performance requirements -> Review strategic denormalization
```

## Strategic Denormalization Patterns

### When to Denormalize?
- When the read-to-write ratio is very high
- Frequent queries requiring 5+ JOINs
- When real-time aggregation/statistics are needed
- Dashboard/report-specific data

### Denormalization Pattern Catalog

| Pattern | Description | Suitable For | Trade-offs |
|---------|-------------|-------------|------------|
| **Derived column** | Store computed values (`total_price`) | Frequent sum lookups | Requires sync on update |
| **Duplicated column** | Copy frequently used FK target columns | Avoiding JOINs | Data inconsistency risk |
| **Pre-joined table** | Materialize join results as physical table | Reports/dashboards | Storage space, update complexity |
| **History snapshot** | Preserve point-in-time data (`order_address`) | Storing address at time of order | Storage space |
| **Counter column** | `likes_count`, `comments_count` | Real-time count display | Concurrency handling |
| **JSON/JSONB** | Unstructured extension data | Settings, metadata | Indexing limitations |

## Common Domain ERD Patterns

### E-Commerce
```
users ──1:N──> orders ──1:N──> order_items
  │                                 │
  └──1:N──> addresses          products
  └──1:N──> reviews ──N:1──────┘
                               │
products ──N:M──> categories (via product_categories)
products ──1:N──> product_images
products ──1:N──> product_variants
```

Core tables:
- `users` (id, email, name, password_hash, created_at)
- `products` (id, name, description, base_price, status)
- `orders` (id, user_id, status, total, shipping_address_snapshot)
- `order_items` (id, order_id, product_id, variant_id, quantity, unit_price)

### SaaS Multi-Tenant
```
tenants ──1:N──> users ──N:M──> roles (via user_roles)
   │                              │
   └──1:N──> subscriptions    permissions ──N:M──> roles
   └──1:N──> [domain tables] (tenant_id FK)
```

Key point: Include `tenant_id` in all business tables; apply RLS (Row Level Security)

### Social Network
```
users ──N:M──> users (via follows: follower_id, following_id)
  │
  └──1:N──> posts ──1:N──> comments
  │              └──N:M──> tags (via post_tags)
  │              └──1:N──> likes (user_id + post_id UNIQUE)
  └──1:N──> messages (sender_id, receiver_id)
```

### CMS/Blog
```
users ──1:N──> posts ──N:M──> tags (via post_tags)
                 │
                 └──1:N──> comments (self-referencing: parent_id)
                 └──1:N──> media
                 └──1:1──> post_meta (SEO, OG tags, etc.)
```

## Relationship Patterns

### 1:1 Relationship
- Large table splitting (frequently used columns vs rarely used columns)
- Optional extension (`user` + `user_profile`)
- Implementation: FK + UNIQUE constraint

### 1:N Relationship
- Most common relationship type
- Self-referencing: category trees, comment threads (`parent_id`)
- Implementation: FK on the child table

### M:N Relationship
- Junction table required
- Junction table may include additional attributes (`created_at`, `role`, `quantity`)
- Naming: `{table1}_{table2}` or a meaningful name (`enrollments`)

## Common Column Patterns

### Default Timestamps
Include in all tables:
- `id` — UUID or BIGINT AUTO_INCREMENT
- `created_at` — TIMESTAMPTZ DEFAULT NOW()
- `updated_at` — TIMESTAMPTZ, auto-updated via trigger

### Soft Delete
- `deleted_at` — TIMESTAMPTZ NULL (NULL means not deleted)
- All queries include `WHERE deleted_at IS NULL` condition
- Enables restoration and serves as audit trail

### Status Management
- `status` — ENUM or VARCHAR
- State transition rules must be documented (which states can transition to which)
- If history is needed, use a separate `status_history` table

### Internationalization
- Strategy 1: Column extension (`name_ko`, `name_en`, `name_ja`)
- Strategy 2: Translation table (`product_translations`: product_id, locale, name, description)
- Strategy 2 recommended (no schema changes needed when adding languages)