sqlschemae-commercedatabase-schemaentity-attribute-value

Designing a SQL schema for a combination of many-to-many relationship (variations of products)


I hope the title is somewhat helpful. I'm using MySQL as my database

I am building a database of products and am not sure how to handle storing prices/SKU of variations of a product. A product may have unlimited variations, and each variation combination has its own price/SKU/etc..

This is how I have my products/variations table set up at the moment:

PRODUCTS
+--------------------------+
| id | name | description  |
+----+------+--------------+
| 1  | rug  | a cool rug   |
| 2  | cup  | a coffee cup |
+----+------+--------------+

PRODUCT_VARIANTS
+----+------------+----------+-----------+
| id | product_id | variant  | value     |
+----+------------+----------+-----------+
| 1  | 1          | color    | red       |
| 2  | 1          | color    | blue      |
| 3  | 1          | color    | green     |
| 4  | 1          | material | wool      |
| 5  | 1          | material | polyester |
| 6  | 2          | size     | small     |
| 7  | 2          | size     | medium    |
| 8  | 2          | size     | large     |
+----+------------+----------+-----------+

(`products.id` is a foreign key of `product_variants.product_id`)

I've created an SQLFiddle with this sample data: http://sqlfiddle.com/#!2/2264d/1

The user is allowed to enter any variation name (product_variants.variant) and can assign any value to it (product_variants.value). There should not be a limit the amount of variations/values a user may enter.

This is where my problem arises: storing prices/SKU for each variation without adding a new table/column every time someone adds a product with a variant that did not exist before.

Each variant may have the same price but the SKU is unique to each product. For example Product 1 has 6 different combinations (3 colors * 2 materials) and Product 2 only has 3 different combination (3 sizes * 1).

I've thought about storing the combinations as a text, i.e:

+------------+-----------------+-------+------+
| product_id | combination     | price | SKU  |
+------------+-----------------+-------+------+
| 1          | red-wool        | 50.00 | A121 |
| 1          | red-polyester   | 50.00 | A122 |
| 1          | blue-wool       | 50.00 | A123 |
| 1          | blue-polyester  | 50.00 | A124 |
| 1          | green-wool      | 50.00 | A125 |
| 1          | green-polyester | 50.00 | A125 |
| 2          | small           | 4.00  | CD12 |
| 2          | medium          | 4.00  | CD13 |
| 2          | large           | 3.50  | CD14 |
+------------+-----------------+-------+------+

But there must be a better, normalized, way of representing this data. Hypothetical situation: I want to be able to search for a blue product that is less than $10. With the above database structure it is not possible to do without parsing the text and that is something I want to avoid.

Any help/suggestions are appreciated =)


Solution

  • Applying normalization to your problem, the solution is as given. Run and see it on SQL Fiddle.

    CREATE TABLE products (
        product_id  int AUTO_INCREMENT PRIMARY KEY,
        name        varchar(20),
        description varchar(30)
    );
    
    INSERT INTO products
        (name, description)
    VALUES
        ('Rug', 'A cool rug' ),
        ('Cup', 'A coffee cup');
    
    -- ========================================
    
    CREATE TABLE variants (
        variant_id int AUTO_INCREMENT PRIMARY KEY,
        variant    varchar(50)
    );
    
    INSERT INTO variants
        (variant)
    VALUES
        ('color'),
        ('material'),
        ('size');
    
    -- ========================================
    
    CREATE TABLE variant_value (
        value_id   int AUTO_INCREMENT PRIMARY KEY,
        variant_id int,
        value      varchar(50)
    );
    
    INSERT INTO variant_value
        (variant_id, value)
    VALUES
        (1, 'red'),
        (1, 'blue'),
        (1, 'green'),
        (2, 'wool'),
        (2, 'polyester'),
        (3, 'small'),
        (3, 'medium'),
        (3, 'large');
    
    -- ========================================
    
    CREATE TABLE product_variants (
        product_variants_id int AUTO_INCREMENT PRIMARY KEY,
        product_id          int,
        productvariantname  varchar(50),
        sku                 varchar(50),
        price               float
    );
    
    INSERT INTO product_variants
        (product_id, productvariantname, sku, price)
    VALUES
        (1, 'red-wool', 'a121', 50),
        (1, 'red-polyester', 'a122', 50);
    
    -- ========================================
    
    CREATE TABLE product_details (
        product_detail_id   int AUTO_INCREMENT PRIMARY KEY,
        product_variants_id int,
        value_id            int
    );
    
    INSERT INTO product_details
        (product_variants_id, value_id)
    VALUES
        (1, 1),
        (1, 4),
        (2, 1),
        (2, 5);