Best Practices for Storing Set Combinations

From: Louis Aslett <louis_at_6internet.com>
Date: 3 Apr 2004 06:29:58 -0800
Message-ID: <88adeb46.0404030629.340e0594_at_posting.google.com>



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 Received on Sat Apr 03 2004 - 16:29:58 CEST

Original text of this message