1

Currently, my Inventory system is writing products data like this:

  1. T-Shirt Gildan -> blue, L, short -> stock: 140 -> price: $40
  2. T-Shirt Gildan -> blue, XL, short -> stock: 0 -> price: $45
  3. T-Shirt RedMango -> blue, L, short -> stock : 100 -> price: $30
  4. T-Shirt RedMango -> red, L, short -> stock : 140 -> price: $30
  5. T-Shirt RedMango -> red, L, long -> stock : 100 -> price: $35

Basically, I want to create a website like products catalog. e.g user flow:

  1. User choose a T-Shirt Gildan product (options: T-Shirt Gildan or T-Shirt RedMango)
  2. User choose blue color: (option: only blue)
  3. User choose XL size: (options: L or XL, inform the user if XL size is out of stock)

To achieve that flow, I'm try to normalize above data. Then I'm to split to some tables. Here's my current work.

Table product

---------------------
id | name
---------------------
1  | T-Shirt Gildan
2  | T-Shirt RedMango

Table attr

------------
id | name
------------
1  | color
2  | size
3  | sleeve

Table attr_value

---------------------------------------
id | attr_variant_id | attr_id | value
---------------------------------------
1  |        1        |    1    | blue
2  |        1        |    2    | L
3  |        1        |    3    | short 
4  |        2        |    1    | blue
5  |        2        |    2    | XL
6  |        2        |    3    | short

Table attr_variant

------------------------------------------------
id | product_id |       sku      | price | stock
------------------------------------------------
1  |     1      |  #Blue#L#Short | $40   | 140
2  |     1      | #Blue#XL#Short | $45   | 0

Is that tables enough for my user flow?

  • Your attr_variant.sku makes the tables attr and attr_value excess. And attr_value.attr_id seems to be wrong. – Akina Feb 4 at 5:43
  • attr_value.attr_id has been fixed. Why tables attr and attr_value excess? – Engkus Kusnadi Feb 4 at 6:16
  • Why tables attr and attr_value excess? 1) Their values may be extracted from sku 2) Attributes are positionally-defined 3) The values in attr and attr_value may theoretically contradict with the same values in sku - and you cannot to build the constraint logic for this integrity check. PS. I have not said that these tables are excess, I have said that this structure of sku makes them excess. – Akina Feb 4 at 6:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.