Product Management System - Technical Documentation

📋 Introduction

This document describes the full architecture of the product management system, designed to support everything from simple products to ones with multiple variants (size, color, flavor, etc.).

🎯 Goal: Provide a clear guide for both end users (who enter data) and developers (who build and maintain the system).

📦 Simple Products

Services, products without variants, one-off items.

1 table Easy

🔄 Products with Variants

Clothing (size/color), food (flavor), electronics (model).

5+ tables Complex

🖼️ Image Management

General images and variant-specific images.

2 tables Dual

📊 Table Structure

Table	Type	Depends on	Description
business_entities	Base	-	Tenants / Businesses (assumed to already exist)
product_categories	Base	business_entities	Product categories
prdcts_cat_attributes	Base	-	Global attribute catalog (Color, Size, etc.)
prdcts_cat_attribute_values	Base	prdcts_cat_attributes	Possible attribute values (Red, S, Strawberry)
products	Master	business_entities, product_categories	Master products (template)
product_images	Image	products	General product images
product_attributes	Config	products, prdcts_cat_attributes	Attributes assigned to a product
product_variants	Variant	products, chzr_measure_units	Specific SKUs with their own price/stock
product_variant_images	Image	product_variants	Variant-specific images
service_products	Parallel	-	Simple services catalog

⚠️ Note: Tables in bold require a strict insertion order. Base tables must exist before inserting data.

🔄 Data Flow Map

┌─────────────────┐
│ business_entities│
│    (Tenant)     │
└────────┬────────┘
         │
         ▼
┌─────────────────┐    ┌─────────────────────────┐
│product_categories│    │prdcts_cat_attributes   │
│                  │    │(Attribute Catalog)     │
└────────┬────────┘    └───────────┬─────────────┘
         │                          │
         ▼                          ▼
┌─────────────────┐    ┌─────────────────────────┐
│    products     │◄───┤prdcts_cat_attribute_    │
│ (Master Product)     │values (Attribute Values) │
└────────┬────────┘    └─────────────────────────┘
         │                          ▲
         │                          │
         ▼              ┌───────────┴─────────────┐
┌─────────────────┐     │product_attributes       │
│ product_images  │     │(Product-Attribute Link) │
│ (General Images)│     └─────────────────────────┘
└────────┬────────┘
         │
         ▼
┌─────────────────┐    ┌─────────────────────────┐
│product_variants │◄───┤ (Attribute value        │
│   (SKUs)        │    │  combinations)          │
└────────┬────────┘    └─────────────────────────┘
         │
         ▼
┌─────────────────┐    ┌─────────────────────────┐
│product_variant_ │    │ service_products        │
│images (Variant  │    │ (Services Catalog)      │
│ Images)         │    │                         │
└─────────────────┘    └─────────────────────────┘

➜ MAIN FLOW (physical products/variants)
➜ ALTERNATE FLOW (services, simplified)

Table dependency diagram. Arrows mean "depends on".

📌 Data Capture Order

To maintain referential integrity, follow this strict order:

1

Base Tables (Independent)

prdcts_cat_attributes - Create global attributes (Color, Size, Flavor)

prdcts_cat_attribute_values - Create values (Red, S, Strawberry)

✓ Don't depend on other tables

2

Master Product

products - Insert the template product

ⓘ Depends on: business_entities, product_categories (assumed to exist)

3

General Images (Optional)

product_images - Insert master product images

ⓘ Depends on: products

4

Attribute Assignment

product_attributes - Link attributes to the product

ⓘ Depends on: products, prdcts_cat_attributes

5

Variant Creation

product_variants - Create specific SKUs (combinations)

ⓘ Depends on: products, chzr_measure_units

6

Specific Images

product_variant_images - Insert per-variant images

ⓘ Depends on: product_variants

7

Services (Parallel)

service_products - Any time

✓ Fully independent

🎯 Main Use Cases

📱 Simple Product

Example: Consulting service, book, online course

Only uses products
Optional: product_images
Doesn't need variants or attributes

👕 Product with Variants

Example: T-shirt (Color + Size)

Uses the ENTIRE full flow
Attributes: Color, Size
Variants: Red/S, Red/M, Blue/S...
Color-specific images

🍬 Food Product

Example: Gummies (Flavor)

Attribute: Flavor
Variants: Strawberry, Chocolate
Price can vary by flavor
Independent stock per flavor

🖼️ Dual Image Management

Example: Clothing catalog

product_images: Model, packaging, sizes
product_variant_images: Specific color
Both complement each other

💾 Full SQL Examples

📌 Example 1: Simple Product (Service)

-- 1. Create the master product INSERT INTO products (id_code, entity_id, category_id, brand_id, sku, name, description, material, status, tax_code, iva, unit_type, price, cost, ump, ums, is_active, points, xtra_points) VALUES ('SRV-CONS-001', 1, 1, 0, 'CONS-IT-HORA', 'IT Consulting per hour', 'On-site specialized technical support', 'N/A', 1, '01010101', 16, 'Hour', 850.00, 300.00, 'H', 'H', 1, 0, 0); SET @product_id = LAST_INSERT_ID(); -- 2. Add a representative image INSERT INTO product_images (product_id, image_url, image_order, is_primary, alt_text) VALUES (@product_id, 'https://example.com/img/it-consulting.jpg', 0, 1, 'IT Consulting - Technical support');

📌 Example 2: Product with Variants (T-shirt)

-- 1. Make sure global attributes exist INSERT INTO prdcts_cat_attributes (attribute_name, attribute_type, is_variant, display_order) VALUES ('Color', 'color', 1, 1), ('Size', 'list', 1, 2); SET @attr_color_id = 1; -- Adjust to real IDs SET @attr_size_id = 2; -- 2. Insert attribute values INSERT INTO prdcts_cat_attribute_values (attribute_id, value_text, value_extra, display_order) VALUES (@attr_color_id, 'Red', '#FF0000', 1), (@attr_color_id, 'Blue', '#0000FF', 2), (@attr_size_id, 'S', NULL, 1), (@attr_size_id, 'M', NULL, 2), (@attr_size_id, 'L', NULL, 3); -- 3. Create the master product INSERT INTO products (id_code, entity_id, category_id, brand_id, sku, name, description, material, status, tax_code, iva, unit_type, price, cost, ump, ums, is_active, points, xtra_points) VALUES ('PRD-PLAY-001', 1, 1, 0, 'PLAY-BASIC', 'Premium Basic T-shirt', '100% cotton t-shirt', 'Cotton', 1, '01010102', 16, 'Piece', 199.99, 80.00, 'PC', 'PC', 1, 0, 0); SET @product_id = LAST_INSERT_ID(); -- 4. General images INSERT INTO product_images (product_id, image_url, is_primary) VALUES (@product_id, 'https://example.com/tshirt-model.jpg', 1), (@product_id, 'https://example.com/tshirt-sizes.jpg', 0); -- 5. Assign attributes to the product INSERT INTO product_attributes (product_id, attribute_id, is_required) VALUES (@product_id, @attr_color_id, 1), (@product_id, @attr_size_id, 1); -- 6. Create variants (combinations) -- Variant: Red + S INSERT INTO product_variants (product_id, sku, variant_code, presentation, measure_unit_id, quantity, price, cost, stock, min_stock, max_stock, weight, barcode, is_active) VALUES (@product_id, 'PLAY-ROJO-S', 'RED-S', 'Red T-shirt Size S', 1, 1.000, 199.99, 80.00, 50, 5, 100, 200.00, '750123456001', 1); SET @variant1_id = LAST_INSERT_ID(); -- Variant: Blue + M INSERT INTO product_variants (product_id, sku, variant_code, presentation, measure_unit_id, quantity, price, cost, stock, min_stock, max_stock, weight, barcode, is_active) VALUES (@product_id, 'PLAY-AZUL-M', 'BLUE-M', 'Blue T-shirt Size M', 1, 1.000, 199.99, 80.00, 30, 5, 100, 220.00, '750123456002', 1); SET @variant2_id = LAST_INSERT_ID(); -- 7. Variant-specific images INSERT INTO product_variant_images (variant_id, image_url, is_primary, alt_text) VALUES (@variant1_id, 'https://example.com/tshirt-red-s.jpg', 1, 'Red T-shirt Size S - Front'), (@variant2_id, 'https://example.com/tshirt-blue-m.jpg', 1, 'Blue T-shirt Size M - Front');

📌 Example 3: Food Product (Gummies)

-- 1. Make sure the Flavor attribute exists INSERT INTO prdcts_cat_attributes (attribute_name, attribute_type, is_variant, display_order) VALUES ('Flavor', 'list', 1, 1); SET @attr_flavor_id = 3; -- Adjust INSERT INTO prdcts_cat_attribute_values (attribute_id, value_text, display_order) VALUES (@attr_flavor_id, 'Strawberry', 1), (@attr_flavor_id, 'Chocolate', 2), (@attr_flavor_id, 'Vanilla', 3); -- 2. Master product INSERT INTO products (id_code, entity_id, sku, name, description, material, price, cost, ...) VALUES ('ALI-GOM-001', 1, 'GOMITAS-FRUT', 'Fruit Gummies', '100g bag', 'Sugar', 25.50, 12.00, ...); SET @product_id = LAST_INSERT_ID(); -- 3. Assign attribute INSERT INTO product_attributes (product_id, attribute_id, is_required) VALUES (@product_id, @attr_flavor_id, 1); -- 4. Variants by flavor INSERT INTO product_variants (product_id, sku, presentation, price, cost, stock, ...) VALUES (@product_id, 'GOM-FRESA', 'Strawberry Gummies 100g', 25.50, 12.00, 200, ...), (@product_id, 'GOM-CHOCO', 'Chocolate Gummies 100g', 27.50, 14.00, 150, ...);

📌 Full Transaction (All-in-One)

START TRANSACTION; -- 1. Master product INSERT INTO products (id_code, entity_id, sku, name, price, ...) VALUES ('TEST-001', 1, 'TEST-SKU', 'Test Product', 100.00, ...); SET @product_id = LAST_INSERT_ID(); -- 2. General image INSERT INTO product_images (product_id, image_url, is_primary) VALUES (@product_id, 'img.jpg', 1); -- 3. Product attributes INSERT INTO product_attributes (product_id, attribute_id, is_required) VALUES (@product_id, 1, 1); -- 4. Variants INSERT INTO product_variants (product_id, sku, price, stock) VALUES (@product_id, 'VAR-1', 100.00, 10); SET @var1_id = LAST_INSERT_ID(); INSERT INTO product_variants (product_id, sku, price, stock) VALUES (@product_id, 'VAR-2', 110.00, 5); SET @var2_id = LAST_INSERT_ID(); -- 5. Variant images INSERT INTO product_variant_images (variant_id, image_url, is_primary) VALUES (@var1_id, 'var1.jpg', 1), (@var2_id, 'var2.jpg', 1); COMMIT;

⚠️ Important Considerations

🔴 Deletion Order (Reverse of insertion):

1. DELETE FROM product_variant_images WHERE variant_id IN (SELECT id FROM product_variants WHERE product_id = X);
2. DELETE FROM product_variants WHERE product_id = X;
3. DELETE FROM product_attributes WHERE product_id = X;
4. DELETE FROM product_images WHERE product_id = X;
5. DELETE FROM products WHERE id = X;
6. (Optional) Clean up unused attributes

🟢 ON DELETE CASCADE: Some tables have CASCADE configured. For example, deleting a master product will automatically delete its variants and images. Be careful!

📌 Recommendations:

Always use transactions for complex inserts
Capture IDs with LAST_INSERT_ID() to use in child tables
For products without variants, ignore the whole variant system
General images (product_images) and specific ones (product_variant_images) can coexist
service_products is independent — use it for quick service catalogs

🔍 Frequently Asked Questions

Can I have images in both product_images and product_variant_images for the same product?

Yes, this is fully valid and recommended. General images show the product in context, specific ones show the exact variant.

What happens if a product has no variants?

Simply don't use the attribute or variant tables. The product lives in products and its images in product_images.

How do I know which attributes are variants?

The is_variant field in prdcts_cat_attributes indicates whether the attribute generates variants (1) or is purely descriptive (0).

Can I switch a simple product to variants later?

Yes, but it will require data migration. It's easier to design it from the start with the full structure.

Product Management System | Technical Documentation