I hope this is the correct newsgroup for this query (if not please
give me a pointer to where is best):
I understand the theory of normalisation etc and am trying to follow
best practices in the design of the database for a new project, but I
am unsure as to the best practice when one wants to store data
relating to combinations of arbitrary numbers of sets of data. For
example, take the following two groups of sets, each containing
elements of data which can be combined together in different ways,
each combination requiring a field of information to be related to it:
Group 1
-------
Set 1: a, b and c
Set 2: i and j
Set 3: w, x, y and z
Possible combinations:
a/i/w, a/i/x, a/i/y, a/i/z, a/j/w .... (24 combinations in total)
Group 2
-------
Set 1: A, B
Set 2: I, J and K
A/I, A/J, A/K, B/I .... (6 combinations in total)
So I may need to store 7 for the combination a/i/w and 2 for the
combination a/i/x etc. But at the same time I have to store perhaps 4
for A/I and 9 for A/J etc.
What is the best way of storing this in a database when the number of
sets *and* the number of elements in those sets may vary?
I cannot structure my table as:
element1 | element2 | element3 | element4 | comb_data
a | i | w | | 7
a | i | x | | 2
. | . | . | . | .
. | . | . | . | .
. | . | . | . | .
A | I | | | 4
A | J | | | 9
. | . | . | . | .
. | . | . | . | .
. | . | . | . | .
because I immediately limit the number of sets I can handle and
introduce potentially redundant columns.
Is there a best practices way to handle this scenario?
Many thanks in advance,
Louis