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?

3
  • Your attr_variant.sku makes the tables attr and attr_value excess. And attr_value.attr_id seems to be wrong.
    – Akina
    Commented Feb 4, 2020 at 5:43
  • attr_value.attr_id has been fixed. Why tables attr and attr_value excess? Commented Feb 4, 2020 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
    Commented Feb 4, 2020 at 6:52

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.