# Product Variant Database Architecture

## 📋 Table of Contents

1. [Overview](#overview)
2. [Table Structure Explanation](#table-structure-explanation)
3. [Real-World Examples](#real-world-examples)
4. [Data Flow](#data-flow)
5. [Query Examples](#query-examples)

---

## Overview

This document explains why we need 5 tables to handle product variants effectively. The architecture supports:

- ✅ Simple products (no variants)
- ✅ Complex products with multiple variant options
- ✅ Flexible pricing per branch, channel, and variant
- ✅ Reusable variant types across products

---

## Table Structure Explanation

### 1️⃣ `product_options` - Which variant types does this product use?

**Purpose:** Links a product to the variant types it uses (Size, Color, Material, etc.)

**Columns:**

- `product_id` - Which product
- `variant_type_id` - Which variant type (from global `variant_types` table)
- `position` - Display order (Size first, then Color)

**Why needed?**

- Not all products use the same variant types
- A T-Shirt uses Size + Color
- A Book uses Format (Hardcover/Paperback) + Language
- A Phone uses Storage + Color

**Example Data:**

```sql
-- T-Shirt Product uses Size and Color
INSERT INTO product_options (product_id, variant_type_id, position) VALUES
(1, 1, 1), -- Product 1 uses Size (position 1)
(1, 2, 2); -- Product 1 uses Color (position 2)

-- Phone Product uses Storage and Color
INSERT INTO product_options (product_id, variant_type_id, position) VALUES
(2, 3, 1), -- Product 2 uses Storage (position 1)
(2, 2, 2); -- Product 2 uses Color (position 2)
```

---

### 2️⃣ `product_option_values` - Which specific values are available?

**Purpose:** Links the product's variant types to specific available options

**Columns:**

- `product_option_id` - Which variant type for this product
- `variant_type_option_id` - Which specific option (from global `variant_type_options`)
- `position` - Display order

**Why needed?**

- Not every product offers ALL possible values
- A Premium T-Shirt offers: XS, S, M, L, XL, XXL (6 sizes)
- A Budget T-Shirt offers: M, L, XL (3 sizes only)
- Same variant type, different available values per product

**Example Data:**

```sql
-- T-Shirt offers sizes: S, M, L (not XS or XXL)
INSERT INTO product_option_values (product_option_id, variant_type_option_id, position) VALUES
(1, 2, 1), -- Small
(1, 3, 2), -- Medium
(1, 4, 3); -- Large

-- T-Shirt offers colors: Red, Blue, Black
INSERT INTO product_option_values (product_option_id, variant_type_option_id, position) VALUES
(2, 10, 1), -- Red
(2, 11, 2), -- Blue
(2, 12, 3); -- Black
```

---

### 3️⃣ `product_variants` - Actual SKUs (Inventory Items)

**Purpose:** Stores each unique combination as a sellable item with its own SKU

**Columns:**

- `product_id` - Parent product
- `title` - Human-readable combination (e.g., "Small / Red")
- `sku` - Unique stock keeping unit
- `barcode` - Physical barcode
- `stock_qty` - Inventory quantity
- `track_inventory` - Enable/disable inventory tracking
- Weight, dimensions, etc.

**Why needed?**

- Each combination is a separate inventory item
- Different SKUs for warehouse management
- Independent stock levels per variant
- Unique barcodes for scanning

**Example Data:**

```sql
-- T-Shirt has 9 variants (3 sizes × 3 colors)
INSERT INTO product_variants (product_id, title, sku, stock_qty) VALUES
(1, 'Small / Red', 'TSHIRT-SM-RED', 50),
(1, 'Small / Blue', 'TSHIRT-SM-BLUE', 30),
(1, 'Small / Black', 'TSHIRT-SM-BLACK', 25),
(1, 'Medium / Red', 'TSHIRT-MD-RED', 100),
(1, 'Medium / Blue', 'TSHIRT-MD-BLUE', 80),
(1, 'Medium / Black', 'TSHIRT-MD-BLACK', 90),
(1, 'Large / Red', 'TSHIRT-LG-RED', 60),
(1, 'Large / Blue', 'TSHIRT-LG-BLUE', 40),
(1, 'Large / Black', 'TSHIRT-LG-BLACK', 70);
```

---

### 4️⃣ `product_variant_option_values` - What is each variant made of?

**Purpose:** Links each variant SKU to its specific option values (Small + Red, Medium + Blue, etc.)

**Columns:**

- `product_variant_id` - Which SKU
- `product_option_value_id` - Which specific option value

**Why needed?**

- Defines what each variant actually IS
- Enables filtering: "Show me all Red items"
- Enables variant switching: "Same product in Blue?"
- Powers variant selector UI

**Example Data:**

```sql
-- Variant "Small / Red" is made of:
INSERT INTO product_variant_option_values (product_variant_id, product_option_value_id) VALUES
(1, 1), -- Size: Small (product_option_value_id 1)
(1, 4); -- Color: Red (product_option_value_id 4)

-- Variant "Medium / Blue" is made of:
INSERT INTO product_variant_option_values (product_variant_id, product_option_value_id) VALUES
(4, 2), -- Size: Medium (product_option_value_id 2)
(4, 5); -- Color: Blue (product_option_value_id 5)
```

---

### 5️⃣ `product_prices` - Multi-channel, Multi-branch Pricing

**Purpose:** Stores flexible pricing for different scenarios

**Columns:**

- `product_id` - Base product (for non-variant products)
- `product_variant_id` - Specific variant (for variant products)
- `product_branch_id` - Store/warehouse location
- `channel_name` - Sales channel (POS, Online, Wholesale, etc.)
- `retail_price`, `wholesale_price`, `cost_price`, etc.
- `effective_date_from` / `effective_date_to` - For time-based pricing

**Why needed?**

- Different prices for different sales channels
- Different prices per store location
- Different prices per variant
- Promotional pricing with date ranges
- Quantity-based pricing (wholesale)

**Example Data:**

```sql
-- T-Shirt "Small / Red" - Different prices by location and channel
INSERT INTO product_prices (product_id, product_variant_id, product_branch_id, channel_name, retail_price) VALUES
-- Store 1 (New York)
(1, 1, 1, 'POS', 25.00),           -- In-store price NYC
(1, 1, 1, 'Online Store', 29.99),  -- Online price from NYC
(1, 1, 1, 'Wholesale', 15.00),     -- Wholesale from NYC

-- Store 2 (Los Angeles)
(1, 1, 2, 'POS', 27.00),           -- In-store price LA (higher cost of living)
(1, 1, 2, 'Online Store', 29.99),  -- Online price from LA (same as NYC)
(1, 1, 2, 'Wholesale', 16.00);     -- Wholesale from LA
```

---

## Real-World Examples

### Example 1: T-Shirt with Size and Color

```
Product: Classic Cotton T-Shirt
├─ Variant Types (product_options):
│   ├─ Size (position 1)
│   └─ Color (position 2)
│
├─ Available Values (product_option_values):
│   ├─ Sizes: Small, Medium, Large
│   └─ Colors: Red, Blue, Black
│
├─ Variants (product_variants): 9 total (3×3)
│   ├─ TSHIRT-SM-RED: "Small / Red" (Stock: 50)
│   ├─ TSHIRT-SM-BLUE: "Small / Blue" (Stock: 30)
│   ├─ TSHIRT-SM-BLACK: "Small / Black" (Stock: 25)
│   ├─ TSHIRT-MD-RED: "Medium / Red" (Stock: 100)
│   ├─ ... and 5 more
│
├─ Variant Combinations (product_variant_option_values):
│   ├─ TSHIRT-SM-RED → [Size:Small, Color:Red]
│   ├─ TSHIRT-SM-BLUE → [Size:Small, Color:Blue]
│   └─ ... etc
│
└─ Pricing (product_prices): 27 records (9 variants × 3 channels)
    ├─ TSHIRT-SM-RED - POS: $25.00
    ├─ TSHIRT-SM-RED - Online: $29.99
    ├─ TSHIRT-SM-RED - Wholesale: $15.00
    └─ ... for each variant
```

### Example 2: Smartphone with Storage and Color

```
Product: iPhone 15 Pro
├─ Variant Types:
│   ├─ Storage (position 1)
│   └─ Color (position 2)
│
├─ Available Values:
│   ├─ Storage: 128GB, 256GB, 512GB, 1TB
│   └─ Colors: Natural Titanium, Blue Titanium, White Titanium, Black Titanium
│
├─ Variants: 16 total (4×4)
│   ├─ IPHONE15-128-NAT: "128GB / Natural Titanium" (Stock: 20)
│   ├─ IPHONE15-256-BLUE: "256GB / Blue Titanium" (Stock: 15)
│   └─ ... 14 more
│
└─ Pricing: Different price per storage size
    ├─ 128GB variants: $999
    ├─ 256GB variants: $1099
    ├─ 512GB variants: $1299
    └─ 1TB variants: $1499
```

### Example 3: Simple Product (No Variants)

```
Product: Premium Leather Wallet
├─ Variant Types: NONE
├─ Available Values: NONE
├─ Variants: NONE (product_variants is empty)
├─ Variant Combinations: NONE
└─ Pricing: Single price record
    ├─ Branch 1 - POS: $79.99
    ├─ Branch 1 - Online: $89.99
    └─ Branch 2 - POS: $84.99
```

---

## Data Flow

### Creating a Product with Variants

```
1. Create Product Record
   └─ products table

2. Define Which Variant Types This Product Uses
   └─ product_options table
      Example: This T-Shirt uses "Size" and "Color"

3. Define Which Values Are Available
   └─ product_option_values table
      Example: Sizes [S, M, L], Colors [Red, Blue, Black]

4. Generate All Variant Combinations
   └─ product_variants table
      Example: 9 variants (3 sizes × 3 colors)

5. Link Each Variant to Its Options
   └─ product_variant_option_values table
      Example: "TSHIRT-SM-RED" → Size:Small + Color:Red

6. Set Pricing Per Variant/Branch/Channel
   └─ product_prices table
      Example: TSHIRT-SM-RED costs $25 in NYC POS, $29.99 online
```

---

## Query Examples

### 1. Get all available sizes for a product

```sql
SELECT vto.name
FROM product_options po
JOIN product_option_values pov ON pov.product_option_id = po.id
JOIN variant_type_options vto ON vto.id = pov.variant_type_option_id
JOIN variant_types vt ON vt.id = po.variant_type_id
WHERE po.product_id = 1
  AND vt.title = 'Size'
ORDER BY pov.position;
```

### 2. Get all variants with stock for a specific size and color

```sql
SELECT pv.*
FROM product_variants pv
JOIN product_variant_option_values pvov1 ON pvov1.product_variant_id = pv.id
JOIN product_option_values pov1 ON pov1.id = pvov1.product_option_value_id
JOIN variant_type_options vto1 ON vto1.id = pov1.variant_type_option_id
JOIN product_variant_option_values pvov2 ON pvov2.product_variant_id = pv.id
JOIN product_option_values pov2 ON pov2.id = pvov2.product_option_value_id
JOIN variant_type_options vto2 ON vto2.id = pov2.variant_type_option_id
WHERE pv.product_id = 1
  AND vto1.name = 'Medium'
  AND vto2.name = 'Red'
  AND pv.stock_qty > 0;
```

### 3. Get pricing for a variant in a specific branch and channel

```sql
SELECT pp.*
FROM product_prices pp
WHERE pp.product_id = 1
  AND pp.product_variant_id = 4  -- Medium / Red
  AND pp.product_branch_id = 1   -- NYC Store
  AND pp.channel_name = 'POS';
```

### 4. Find all variants that are out of stock

```sql
SELECT pv.sku, pv.title, pv.stock_qty
FROM product_variants pv
WHERE pv.product_id = 1
  AND pv.stock_qty <= pv.reorder_level
ORDER BY pv.stock_qty;
```

### 5. Get cheapest price for any variant of a product

```sql
SELECT MIN(pp.retail_price) as cheapest_price
FROM product_prices pp
WHERE pp.product_id = 1
  AND pp.channel_name = 'Online Store';
```

---

## Why Not Simpler?

### ❌ Option A: Store everything in one table

**Problem:**

- Can't reuse variant types across products
- Difficult to filter by size/color
- Hard to manage inventory per variant
- No flexible pricing

### ❌ Option B: Store variants as JSON

**Problem:**

- Can't query by variant attributes
- Can't join with pricing/inventory
- Poor performance for filtering
- No database constraints

### ✅ Option C: Normalized structure (current)

**Benefits:**

- ✅ Reusable variant types
- ✅ Efficient querying and filtering
- ✅ Independent inventory per variant
- ✅ Flexible pricing structure
- ✅ Data integrity via foreign keys
- ✅ Scalable for complex products

---

## Summary

| Table                           | Purpose                                    | Example                                |
| ------------------------------- | ------------------------------------------ | -------------------------------------- |
| `product_options`               | Which variant types does this product use? | T-Shirt uses Size + Color              |
| `product_option_values`         | Which specific values are available?       | Sizes: S, M, L; Colors: Red, Blue      |
| `product_variants`              | Actual inventory items (SKUs)              | TSHIRT-SM-RED with 50 in stock         |
| `product_variant_option_values` | What is each variant made of?              | TSHIRT-SM-RED = Size:Small + Color:Red |
| `product_prices`                | Multi-channel/branch pricing               | $25 in-store, $29.99 online            |

**All 5 tables work together to create a flexible, scalable product variant system that handles:**

- Simple products (no variants)
- Complex products (multiple variant types)
- Flexible pricing (per branch, channel, variant)
- Inventory management (per variant SKU)
- Reusable variant definitions (global Size, Color types)