My setup is as follows:
Tbl: Listtype (can contain n types)
ID* Type
--------------
1 Color
2 Size
3 Quality
Tbl: ListOptions (can contain n options)
ID, TypeID, Option
--------------------------
-
1 1 Red
2 1 Blue
3 2 Smal
4 2 Large
5 3 Low
6 3 High
Now i want to add a new product, that uses some - or all - of the lists/options
(ie product 1 might have 4 colors, 3 sizes and no quality assignment, product 2 might have 2 colors, 4 sizes and 3 qualities)
If a products uses all lists/options from above, it would generate 8 combinations (2x2x2)
And i need following rows to be generated:
Tbl: Variants (Unique variantID, productID and additions data specifik for the variant)
--------------------------
-----
ID ProductID
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
(This one i have already sorted out, but maybe it needs to be generated in conjunction with the following)
Tbl: VariantOptions
ID* VariantID, OptionID
1 1 1
2 1 3
3 1 5
4 2 1
5 2 3
6 2 6
7 3 1
8 3 4
9 3 5
10 4 1
11 4 4
12 4 6
13 5 2
14 5 3
15 5 5
16 6 2
17 6 3
18 6 6
19 7 2
20 7 4
21 7 5
22 8 2
23 8 4
24 8 6
Im dealing with a large number of combinations, so if possible i would like to avoid cursors/while ect.
Any thoughts is appreciated
