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 =)
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);