E-commerce database structure

If the product with different size/color options are different SKU, they deserve the description and images, in which case just write a simple utility for backend guy to copy it from an existing product.

Or/And, make your product table abstract with only fields which will not change, put all other fields such as size/color/price etc ... in a pivot table, backend guy then simply add these attributes to pivot table.

I'm creating a similar website. I was thinking about an "options" field in the database that is json. If a custom field is added to the product (dropdown etc) then it's stored in the json and I can pull it later.

@jaytee Not recommended. But yes, if you don't want any filter on these options ? How do you plan to search from json string ?

@d3xt3r you can query json structures in recent-ish postgres & mysql :-)

@ohffs Yes, recent-ish is the key word.

@Jaytee That's a possible solution but I'd rather not store JSON in the database, think I'll have to make a 'products' table that contains description and images, then a 'product_sizes' table that contains different size options, price and sku and see how that goes :)

@d3xt3r just thought it was worth mentioning in case anyone ended up with mongodb installed by mistake ;-)

@d3xt3r @ohffs @matt_penton Sorry for hijacking the thread, but how would i approach this? I didn't want to create new fields in my table like:

size, color

etc

As there will be many different options for each product such as a dropdown or select box thus I thought json would be the best approach just to remove unwanted fields from my table if multiple products don't share that feature.

Example: Magento's custom fields.

@matt_panton Hey there.

I would have a products table and a variants table. Each product has many variants.

Now you store any unique information for each product (color, size etc.) in the variants table.

Don't forget, some products may have different size only, some different color only, some have different size and different color, and each of these products can have a different price.. and of course, some products have no variations :)

I've built a few e-commerce sites in old-style PHP many years ago, it was fun :)

@Jaytee I came across this stack overflow thread which looks like it might have a good solution for you, although it does look a bit complicated http://stackoverflow.com/questions/9561928/whats-the-most-elegant-mysql-schema-for-products-options-and-categories