Best way to store Product attributes Like Size, Color etc..

@Sinnbeck this article is really awesome, very informative and complete... A must read.

Thanks a lot for sharing it.

Hello @tykus thanks for replying .. If I understood right, so I would have to write down the same product multiple times and just change variant_id in the products table ?? where variant is different colors or whatever ? and just in the variant table .. I would have attribute_id .. referencing id in attributes table .. am I right or did I miss something.

+--------------+--------+-------+
| id          | product name  | variant_id |
+--------------+--------+-------+
| 1            | White T shirt     | 1 |
| 2           | Red T shirt      | 2 |
| 3           | Blue T shirt      | 3 |
| 4           | Yellow T shirt      | 4 |
| 5           | Green T shirt      | 5 |
+--------------+--------+-------+

Hello @martinbean, thanks for replying .. I think it's quite similar to tykus approach and please correct me if I'm wrong.. so I would have to write down the same product multiple times and just change sku_id in the products table ?? where sku is different colors or sizes or whatever ? and just in the sku table .. I would have attribute_id .. referencing id in attributes table ..

+--------------+--------+-------+
| id          | product name  | sku_id |
+--------------+--------+-------+
| 1            | White T shirt     | 1 |
| 2           | Red T shirt      | 2 |
| 3           | Blue T shirt      | 3 |
+--------------+--------+-------+

attributes table

+--------------+--------+-------+
| id          | attribute_value  
+--------------+--------+-------+
| 1               | Color|
+--------------+--------+-------+

and skus table would be

+--------------+--------+-------+
| id          | attribute_id  | sku_value|
+--------------+--------+-------+
| 1            | 1     | White |
| 2           | 1     | Red |
| 3           | 1     | Blue |

+--------------+--------+-------+

@MahmoudMonem No. You’d only have one product row for each product. But if that product comes in multiple variants (i.e. size, colour) then you would have a SKU representing each combination, like you would in a real world store.

If a bricks-and-mortar store sells t-shirt, and that t-shirt came in multiple sizes, then each size would have its own unique SKU.

@martinbean What I didn't understand when you write "an attribute belongs to a product file" means that the attribute table (size, color, dimension) has a relationship with the product table?

I would suggest that your best bet is to track each variant's sku, price, cost and stock in a table, with a 'belongs to' relation to the overall product record that houses description/etc.

@willvincent I wouldn’t.

“SKU” stands for “stock keeping unit”. It is the unit any stock should be tracked for. When a red, small t-shirt is sold, you deduct the stock for that SKU.

If you just link stock to the top-level product then that serves no use as you don’t know how many small t-shirts you have, how many medium t-shirts you have, how many large t-shirts you have; just that you would have n t-shirts.

@martinbean Perhaps I didn't outline my thought clearly, or you just misunderstood.

A SKU should be tied to the specific variant.

Suppose you have a t-shirt shop, with n designs. and Those designs are available on a variety of shirt size/color combinations.. perhaps more variations if you also offer different shirt types, etc. But a sku should be associated with a specific variant. I think we're really saying the same thing here. 👍

@tatenda And how would you then track say, electrical products that don’t have a size but other attributes? That’s not going to fit in your stock table.

The OP needs:

• A products table to store the top-level product information (name, description, etc).
• A skus table to store variants and their prices.
• An attributes table to define the attributes for a particular product (size, colour, RAM, disk size, etc).
• A attribute_sku pivot table that defines the attribute values for a particular SKU (i.e. a SKU is red).
• If tracking stock, some sort of stock_level_adjustments table that tracks additions and deductions in stock (i.e. delivery received from supplier, units sold in order).

@martinbean I actually like this a lot, but I have a couple of questions. what do you mean by prices in the skus table ? how the structure of the sku table would look like .. because I think if we added the price, then we must reference the product as well .. because for example ; multiple products may have the sku " red". so that's different prices.. OR you mean we can have many " red " skus equal to the total available qty , which all belong to 1 product ? and just connect the skus table with the attribute table .. which has relationship with the products table .. hope if i got u right .. sorry if I sound confused, but actually this thing burnt my brain already haha <3

@MahmoudMonem A product would have many SKUs. Some SKUs may have a different price. For example, a XXL t-shirt might be a couple of dollars more than a S t-shirt because of the extra material needed.

Same with a stick of a RAM. There may be one “ACME DDR RAM” product, but with multiple SKUs for different memory capacities. Obviously a stick of say, 16 GB RAM should cost more than a stick of 8 GB RAM.

@martinbean Alright got it .. thank you, really appreciate your help.

@martinbean I didn't understand a thing. Above you write that you need a skus table where you can insert the "VARIANTS" but what are these variants? attribute values ​​or what?

@MahmoudMonem Were you able in the end to achieve what you wanted?

@mahmoudhussien what about adding qty column inside your SKU table which is decreased by qty number when a purchase is actually completed ..

For example if an SKU has qty of 5 and someone completed purchase for 2 items .. then the qty will be decreased to 3 .. and so on till its 0 .. when the qty is 0 .. this sku will not even appear in the foreach loop because you have a condition qty to be > 0

In that case when you loop through the skus you can loop where sku qty is > 0

Also you can only loop through UNIQUE .. so if you have 5 skus for the color black .. you can only show (first) .. so that when a customer choose that sku and actually buy you update the qty of that apecific sku .. hope this makes sense to u

@mahmoudhussien Either display a list of all available SKUs:

@foreach($skus as $sku)
    <label for="sku-{{ $sku->id }}">
        <input name="sku" type="radio" value="{{ $sku->id }}">
        <span>{{ implode(' / ', $sku->attributes->pluck('value')) }}</span>
    </label>
@endforeach

Or update the available SKUs in realtime using something like JavaScript when a user selects an option.