product-data-modeling

What this skill does

# Product Data Modeling

## Overview

Every platform has its own product data model — Shopify uses products with variants and metafields, WooCommerce uses products with attributes and custom fields, and BigCommerce uses products with options and custom fields. Understanding your platform's model and fitting your catalog into it correctly prevents data quality problems and import failures. Only build a custom data model if you're building a headless storefront from scratch.

## When to Use This Skill

- When designing a product catalog structure for a new store on an existing platform
- When adding variant support (size, color, material) to existing products
- When implementing custom attributes for faceted filtering
- When modeling product relationships (bundles, cross-sells, accessories)
- When importing products from a PIM or ERP into the platform's model

## Core Instructions

### Step 1: Understand your platform's core data model

| Platform | Product | Variants | Custom Attributes | Relationships |
|----------|---------|----------|-------------------|---------------|
| **Shopify** | Product + up to 3 Options, up to 100 Variants | Per-variant: price, SKU, inventory, weight, image | Metafields (standard or custom namespaces) | Collections, cross-sell via apps |
| **WooCommerce** | Product (Simple, Variable, Grouped, External) | Per-variation: price, SKU, stock, attributes | Custom product attributes + WooCommerce custom fields | Upsells, cross-sells (built-in), grouped products |
| **BigCommerce** | Product with Options and Option Sets | Per-variant (modifier/option combination): price, SKU, stock | Custom fields per product | Related products, bundled products |
| **Custom / Headless** | Design from scratch with PostgreSQL/MongoDB | Full control over schema | EAV or JSONB for flexible attributes | Junction tables for relationships |

---

### Step 2: Platform-specific modeling

---

#### Shopify

**Core structure:**
- **Product**: title, description, vendor, product_type, tags, images
- **Options**: up to 3 (e.g., Size, Color, Material) — defines the axes of variation
- **Variants**: one per combination of option values — each has its own price, SKU, inventory, weight
- **Metafields**: custom data per product or variant (e.g., care instructions, sizing guide URL, technical specs)

**Modeling decisions:**

1. **Product vs. Variant**: Put a product into a single product record if customers compare the options side-by-side on one page. Create separate product records for fundamentally different products that happen to share a name.

2. **Option naming**: Shopify limits you to 3 options per product. If you need more (e.g., Size + Color + Material + Length), consider combining two options (e.g., "Size/Width") or using metafields for the 4th dimension.

3. **Metafields for custom attributes**: Go to **Settings → Custom data → Products** to create metafield definitions. Use metafields for attributes that:
   - Don't affect price or inventory (those belong on variants)
   - Are product-specific custom data (care instructions, certifications, dimensions)
   - Need to be displayable in your theme or filterable via a search app

4. **Product types and tags**: Use `product_type` for the primary merchandise category (e.g., "Dress", "Running Shoe") and `tags` for cross-cutting attributes (e.g., `color-navy`, `occasion-formal`, `material-cotton`).

**Example product structure for a shirt:**
- Product: "Organic Cotton T-Shirt"
- Options: Size (XS, S, M, L, XL), Color (White, Black, Navy)
- Variants: 15 combinations (5 sizes × 3 colors), each with SKU and inventory
- Metafields: `care_instructions`, `material_weight_gsm`, `sustainability_cert`

**Bulk field updates via Matrixify:**
- Export products to see all supported columns
- Edit in spreadsheet, re-import to update any field in bulk including metafields

---

#### WooCommerce

**Product types:**
- **Simple**: one SKU, one price — use for products with no variants
- **Variable**: multiple variations based on attributes — use for products with size, color, etc.
- **Grouped**: a collection of simple products shown together — use for product families
- **External/Affiliate**: linked to an external URL — for affiliate products
- **Virtual**: no shipping — for services, subscriptions
- **Downloadable**: digital products

**Attributes and variations:**

1. Go to **Products → Attributes** to define global attributes (shared across all products):
   - Add attribute: **Color** with values: Red, Blue, Black, White
   - Add attribute: **Size** with values: XS, S, M, L, XL
   - Check **Enable archives** to make the attribute browseable

2. On a Variable product, go to **Attributes tab**:
   - Select your global attributes and check which values apply to this product
   - Go to **Variations tab** → Generate variations from all combinations

3. Per-variation settings: set a unique price, SKU, stock, image, and weight for each variation

**Custom fields (product metadata):**

For attributes that aren't variants (e.g., technical specs, certifications):
- Use WooCommerce's built-in **Custom Attributes** on the Attributes tab (non-variation attributes)
- Install **Advanced Custom Fields (ACF)** for more structured custom field management
- Install **Product Add-Ons** for customer-input fields (engraving, personalization)

**Product relationships (built-in):**
- **Upsells**: Go to **Linked Products tab** → Upsells — shown on the product page
- **Cross-sells**: listed in the cart — add under **Linked Products tab** → Cross-sells
- **Grouped products**: use the Grouped product type to link related simple products

---

#### BigCommerce

**Core structure:**
- **Product**: title, description, brand, categories, weight, images
- **Options**: define the axes of variation (Size, Color, Material)
- **Option Sets**: reusable groups of options assigned to multiple products
- **Variants (SKUs)**: combinations of options — each has a unique SKU, price adjustment, weight, and stock
- **Custom fields**: free-form name/value pairs for additional product data

**Modeling decisions:**

1. Go to **Products → Option Sets** to create reusable option sets (e.g., "Clothing Sizes" with XS–3XL) — assign the same set to multiple products instead of recreating options per product.

2. For products with large variant counts: BigCommerce supports up to 600 SKUs per product. Use **Bulk Pricing** to set price rules that apply to variant groups rather than pricing each variant individually.

3. **Custom fields**: Go to **Products → [Product] → Custom Fields tab** to add structured attributes like `Material`, `Care Instructions`, `Warranty Period`. These display in the product detail page and can be used for search.

4. **Modifier options** (customer-configurable at purchase): Use for personalization (engraving text, color choice that doesn't affect stock). Different from variants — modifiers don't generate separate SKUs.

---

#### Custom / Headless

For headless storefronts, design the core schema around the product-options-variants pattern:

```sql
-- Core product tables (PostgreSQL)
CREATE TABLE products (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug          VARCHAR(255) UNIQUE NOT NULL,   -- URL-safe handle
  title         VARCHAR(500) NOT NULL,
  description   TEXT,
  vendor        VARCHAR(255),
  product_type  VARCHAR(255),
  status        VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('active', 'draft', 'archived')),
  tags          TEXT[] DEFAULT '{}',
  created_at    TIMESTAMPTZ DEFAULT now(),
  updated_at    TIMESTAMPTZ DEFAULT now()
);

-- Variants — one per purchasable combination
CREATE TABLE product_variants (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id    UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  sku           VARCHAR(255) UNIQUE,
  title         VARCHAR(500) NOT NULL,  -- e.g., "Red / Large"
  price         NUMERIC(10,2) NOT NULL,  -- Use NUMERIC, not FLOAT, to avoid round