0

I know it may be odd to ask this kind of question but I would be glad to hear your edits and advice on this case.

let's say I have tables named products and properties that things like color , size , volume and ... are stored in properties table. I have trouble how to store properties of my products like a Tshirt having colors blue , green and sizes Large and small.

The way I already tried is using JSON in product table like this :

{color : 'red' , size : 'large' , available : 1} , {color : 'red' , size : 'small' , available : 0 } , {color :blue : size : Medium , available : 1} , ...

but it doesn't seem good since properties would have a lot of attributes and the JSON gets too big Any suggestions would be welcomed

P.S. I'm using laravel to develop My application.

Rick James
  • 135,179
  • 13
  • 127
  • 222
Masoud Haghbin
  • 863
  • 7
  • 13
  • Is the number of properties fix? Do all products have the same properties? – fancyPants Aug 12 '18 at 08:47
  • @fancyPants no it isn't fix and products have different properties , like product A have properties A and B and Product B has properties A , C and D – Masoud Haghbin Aug 12 '18 at 08:49
  • 1
    It seems unlikely that the json approach would get 'too big' - it is a popular method after all. If you decide to use an EAV model instead, I would suggest splitting out the attributes according to value data type. – Strawberry Aug 12 '18 at 09:04
  • @Strawberry thanks for your suggestion . – Masoud Haghbin Aug 12 '18 at 14:40

2 Answers2

0

The JSON will not get "too big".

Have a table with some fields as regular columns, plus the JSON field for all the rest of the miscellany.

The regular columns are there to make searching somewhat easy. Pick which columns based on typical searches. Use MySQL to search on them, then either use JSON tests (if you have a new enough MySQL, or use client code to dig into the JSON to finsh the filtering.

Your sample JSON represents 3 different products, not one. As a separate step (and maybe another table) you can categorize those 3 'products' as 'Tshirts'. Clearly, Tshirt (etc) would be in its own column, not necessarily in the JSON.

More

Rick James
  • 135,179
  • 13
  • 127
  • 222
-2

You need to deal with database relationships in order to accomplish this.

  1. create a table "sizes"

    | id   | name    |
    +----+-----------+
    | 1   | large      |
    | 2   | small      |
    | 3   | medium  |

  2. create a table "colors"

    | id   | name   |
    +----+----------+
    | 1    | red      |
    | 2    | green  |
    | 3    | blue    |

  3. create a table "products"

    | id   | name     |
    +----+------------+
    | 1    | t-shirt     |
    | 2    | trouser   |
    | 3    | jacket     |

  4. finally create table "stock"

    | id  | size_id | color_id | product_id | qty |
    +----+---------+-----------+-------------+------+
    |  1   |     1     |     1       |       1        | 100 | (Red T-Shirt Large - 100 pc)
    |  1   |     3     |     2       |       2        | 200 | (Green Trouser Medium - 200 pc)
    |  1   |     2     |     3       |       3       | 150 | (Blue Jacket Small - 150 pc)

Jaskaran Singh
  • 914
  • 8
  • 8