Based on my experience when I worked for an apparel company, typically that is how it is done. Each item would have its own code. Although you can group items together for one particular style, each item would still have its own number.
I'd have to second mr_vodka,
If your customer cares what size he gets, or what color, then you'd probably want to know how many parts you have of that size and color...it would be a unique product. One record per product with inventory activity in a child table(s).
If I told a customer we had 100Kg of "stuff" when I had 50Kg of green and 50Kg of blue, I'd have a hard time filling the order for 100Kg of blue...you want to know how much you have of what they want.
You can group by size and get a total across multiple variations, group by color across multiple variations...all depends on how you design your Dbase.
Let me even go one step farther and give you an example of the system that I used to deal with:
Let's say you have a shirt with a particular style. In their operation each color for a particular type of style was a new style.
Shirt - Red StyleNum: 123456Y
Shirt - Blue StyleNum: 123456B
Shirt - Green StyleNum: 123456G
Then under each style there were different sizes. Each one of those had a different UPC number.
you'd probably want to know how many parts you have of that size and color..
That's the key question here: if you only need to note that a product is available in certain colors/sizes, then a checkbox would do. But if you track any data that is specific to a variation, then you must have a record for it.
Thanks for the info guys, I was hoping that there was a way around it to 'condense' the variations into a single record by using a unique code for each variation, no problem with that as each will have its own barcode anyway. I was wondering if it would be possible for the variations to be applied within the record, rather than having separate records for each variation maybe by working some sort of table. A 4 x 5 table would surely be easier to implement than creating 20 records?
For example, product no 1000 in sizes 8, 10, 12, 14, 16 across grey, cream, black, taupe would produce a code like 1001-10-grey using the hyphens to indicate the variation - or am I being too optimistic here?
If I am then I'd better get to work!
What I see all of us asking (without directly ASKING, so let me remedy that) is "How are you going to USE this database?"
When your customer orders (Qty=12) Product# 1001-10-grey, what will you then do?
Will you check stock of that product variation? How?
Will you scan the items as you put them in the box? How will that data get captured into the dbase?
Will you reduce your inventory by 12pcs? How?
The answers to all of these is pretty straightforward if you have a stock record for Product# 1001-10-grey.
The answer is not so clear if you have a record for Product# 1001 with multiple variations on that record. When you reduce inventory, what are you reducing inventory of? 1001? A specific variation...how?
The answers to these questions steer the answers to your questions.
Can you lay it out in a table on a single record? Yes.
Can you then use that Dbase for the desired task? It depends on the task you're trying to do...thus my questions.
The database will be used for producing invoices and stock control. Yes it is necessary to have separate identifiers, but inputting individual data for each variation is a massive pain.
For example, last night I was up late crunching this stuff and the worst one was 3 colours, 4 sizes and plain or embellished - 24 different combinations!
I had a look at Checkout, great little programme... except that for some reason the barcodes it produces don't work all the time which renders it useless as I'd still have to produce them independently and that is where the pain begins. Otherwise it produces all of the variations at the touch of a button and that's what I was trying to find out how to do in FM.