Hello,

I am working with my programmer on designing a database to handle a large variety of products. My programmer has hit a snag and is not the kind to ask for help ar advice so I thought I'd place a post and see if we can get some tips.

The basic problem my programmer is having is coming up with a database design for an extremely wide variety of products. We want the ability to assign variants to products for various sizes, colors, etc; and each combination of variants must be it's own SKU which is tracked seperately in inventory. For example, one product might be a T-shirt. This t-shirt will have a description, an image, a vendor, etc. We would also like the ability to assign variants to this product such as size and color; so there will also be a number of SKU's associated with this product, one for every possible size/color combination. In essence we will have product ID's, which relate to a parent group, and SKU's, which relate to specific color/size combinations and are used to track inventory. Still with me...

Anyway, my programmer is having a hard time coming up with a design for all of this, most specifically on how to relate the various tables involved. For example, product information (parent level) might be held in any number of tables, there might be a couple of tables for variants, as well as tables for specific SKU's. Does anybody have either a sample databse of something similar to this, or even an idea of where to start. Like I said earlier, I am not the programmer or the database expert but I understand enough to perhaps relay some ideas to my stubborn partners.

***Update***
I got some more deets from my programmer. The main problems lies in the fact that every product MUST be able to have an unlimited number of variants and each variant group must allow for an unlimited number of variant options. That is: a shirt must be able to have size, color, material, and on and on for as many variants as I want. Then within colors I must be able to have as many colors as I want.

So, with this in mind the best way to phrase this quesion is: How does the (parent)product ID and the unique combination of variant options for each product relate to the item (SKU). This is difficult because the number of variants options for each variant is not determined and could be any number. I.E.-you can't just have one anormous item table with all the variants because the number of variants is not known.

Thanks in advance.
James

I don't think you can define a rigid coding structure to the variants of the item. IE you may have to reuse the same codes for different products. Many coding structures fall down oveer time when new variants are introduced. The only clear way I could see to do it is to have the SKU/EAN defined, add some digits after the SKU and decode that in a sub table

ie keep the SKU as a unique identifier, sub analyse where you might incorporate other elements such as size, colour, design, fabric, finish etc. It might be worthwhile to use the fulltext index on a product description to allow searches for say "red" "tee-shirt"

I always remember being told that the key or unique reference number shouldn't by iteself have any intrinsic meaning unless you can truly capture all elements which make it unique.

Create a variants table and a ProductSpecs table, or something similar

Eg:

Variant
------
variant_id
description

ProductSpecs
----------
product_spec_id
sku
variant_id
value

Then in your spec table, you could make one entry for each variation you want to assign to a given item. Say you have two variants for color, size for example, you could have something like this:
Then if you need to add/remove or remove variants from a given item, it's pretty easy. For more variants, you simply create the variant you need to spec out in the variants table, then make an entry in your ProductSpecs table hooking up the new variant spec to your item with a value. If you want to get REALLY crazy, you can create another table that will define allowable variants for a given sku or parent item.

Does that make any sense?