Variants, SKU's, and Inventory

--------------------------------------------------------------------------------

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

Do you have a database expert on this project at all? I am rather alarmed that a programmer appears to be designing the database!

In a small enough shop, the programmer IS the database expert! Been there, done that, got the blood-stained tee-shirt to prove it!

-PatP

I understand your alarm. From what I've read it sounds like we are trying to do some heavy duty stuff. My programmer has a a Computer Science degree from a very very good school and and rates his programming skills as an A+ and his database design skills at a B. That is why we are looking for tips on this. However, we are a small company ( 6 employees) and don't have the money or the workload to hire a guy just for database design. My programmer has assured me that once we get he structure down he will be able to program the rest of the software effectively.

Thanks again for any tips!!

James