Geek Answers Handbook

Stock options management with stock options

I am trying to create an inventory management scheme where I can track the number of different options related to products. A product can have any number of options, but for this example I will use the "size" and "color" options.

I came up with three tables:

CREATE TABLE shop_options (
  option_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  option_name VARCHAR(40) NOT NULL,

  PRIMARY KEY (option_id)
);
INSERT INTO shop_options (option_id, option_name) VALUES (1, 'Size');
INSERT INTO shop_options (option_id, option_name) VALUES (2, 'Color');

CREATE TABLE shop_option_properties (
  prop_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  prop_name VARCHAR(40) NOT NULL,

  PRIMARY KEY (prop_id)
);
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (1, 'XS');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (2, 'S');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (3, 'M');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (4, 'L');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (5, 'XL');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (6, 'White');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (7, 'Black');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (8, 'Red');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (9, 'Green');
INSERT INTO shop_option_values (prop_id, prop_name) VALUES (10, 'Blue');

CREATE TABLE shop_product_options (
  product_id INTEGER UNSIGNED NOT NULL,
  option_id INTEGER UNSIGNED NOT NULL,
  prop_id INTEGER UNSIGNED DEFAULT NULL,
  surcharge DECIMAL(7,2) NOT NULL DEFAULT '0.00',
  stock INTEGER UNSIGNED DEFAULT NULL, /* NULL = stock is not limited */

  FOREIGN KEY (product_id)
    REFERENCES shop_products(product_id),
  FOREIGN KEY (option_id)
    REFERENCES shop_options(option_id),
  FOREIGN KEY (prop_id)
    REFERENCES shop_option_properties(prop_id)
);

I decided that this would not work, because I could have “ten common small items” in stock and “ten common white items” in stock, but not “ten common small white items” in stock.

How can I improve my scheme to correctly track the stock for each option that a product may have?

EDIT

, , . , . , shop_product_options:

CREATE TABLE shop_product_options (
  po_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INTEGER UNSIGNED NOT NULL,
  option_id INTEGER UNSIGNED NOT NULL,
  prop_id INTEGER UNSIGNED NOT NULL,
  surcharge DECIMAL(7,2) UNSIGNED NOT NULL DEFAULT '0.00',
  stock INTEGER UNSIGNED DEFAULT NULL,

  PRIMARY KEY (po_id, product_id, option_id, prop_id),
  FOREIGN KEY (product_id)
    REFERENCES shop_products(product_id),
  FOREIGN KEY (option_id)
    REFERENCES shop_options(option_id),
  FOREIGN KEY (prop_id)
    REFERENCES shop_option_properties(prop_id)
);

po_id "" :

INSERT INTO shop_products (product_id, title) VALUES (1, 'Womens Shoe');

INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
  VALUES (1, 1, 1, 3, '0.00', 10);
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
  VALUES (1, 1, 2, 9, '0.50', 20);
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
  VALUES (2, 1, 1, 5, '1.00', 30);
INSERT INTO shop_product_options (po_id, product_id, option_id, prop_id, surcharge, stock)
  VALUES (2, 1, 2, 9, '0.75', 40);

SELECT t1.po_id, t2.title, t3.option_name, t4.prop_name, t1.surcharge, t1.stock FROM shop_product_options AS t1
  JOIN shop_products AS t2 ON t1.product_id = t2.product_id
  JOIN shop_options AS t3 ON t1.option_id = t3.option_id
  JOIN shop_option_properties AS t4 ON t1.prop_id = t4.prop_id
WHERE t1.product_id = 1 ORDER BY t1.po_id ASC;

M XL XL .

+4
2

, ( 6NF 3NF) .
, "".
( AKA SaaS)

- SqlFiddle

enter image description here

:

, . , . , , ProductSpecification ProductTypeOptions, OptionValue Product.
, OptionValue (OptionId + ValueName)
, ID , , , - , -.

Product-Specification, -:

  • ProductSpecification {OptionValue.optionId = productTypeOption.optionId}, , "" "".
  • ProductSpecification {product.productTypeId = productTypeOption.productTypeId}, "Nike" "".

, ( ).
, .
BTW , , . : .

, ?

, "Mens Shoe" "Nike" , , Product.

+5

-, ...

SKU (. 4), :

colors
    id              unsigned int(P)
    description     varchar(10)

+----+-------------+
| id | description |
+----+-------------+
|  1 | White       |
|  2 | Blue        |
|  3 | Green       |
| .. | ........... |
+----+-------------+

items
    id              unsigned int(P)
    description     varchar(20)

+----+-------------+
| id | description |
+----+-------------+
|  1 | T-shirt     |
|  2 | Pencil      |
| .. | ........... |
+----+-------------+

sizes
    id              unsigned int(P)
    description     varchar(10)

+----+-------------+
| id | description |
+----+-------------+
|  1 | Small       |
|  2 | Medium      |
|  3 | Large       |
| .. | ........... |
+----+-------------+

SKU S1C1I1 - , S2C3I1 - .

products
    id              unsigned int(P)
    sku             varchar(50)
    price           double
    quantity        unsigned int

+----+--------+-------+----------+
| id | sku    | price | quantity |
+----+--------+-------+----------+
|  1 | S1C1I1 | 10.00 |      312 |
|  2 | S2C3I1 | 11.00 |       52 |
| .. | ...... | ..... | ........ |
+----+--------+-------+----------+

UPC, EAN ..

0

More articles:


All Articles