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

Correct design is done by understanding the business rules, not from notions
as to what combinations or sets of data need to be stored together.
Re-express your problem in buisness terms, and the solution will probably be
very easy. For example,

An employee can work for one and only one department. A department may have
one or more employees. A project can be worked on by one or more employees
from one or more departments. Etc...




"Louis Aslett" <louis (AT) 6internet (DOT) com> wrote

louis (AT) 6internet (DOT) com (Louis Aslett) wrote in message news:<88adeb46.0404030526.33c55ff7 (AT) posting (DOT) google.com>...
Google for "permutation enumeration" and "factorial base". Basically,
given a set of say 3 elements {a,b,c} there is a "natural" mapping of
integers to all possible combinations

(a,b,c)
(b,a,c)
(a,c,b)
(c,b,a)
(c,a,b)
(b,c,a)

louis (AT) 6internet (DOT) com (Louis Aslett) wrote in message news:<88adeb46.0404030526.33c55ff7 (AT) posting (DOT) google.com>...
Well, first, I don't think there are too many real-world applications
that deal with arbitrary SETS.

But given your example, your sets seem to be attributes of the group.
So it looks to me like you have candidates for two tables. The table
representing Group 1 would have 4 attributes and the one representing
Group 2 would have 3.


The "best practices" on this is to have a good data model defining
data objects, their attributes and their relations. "People" have
names and birthdates. "Cars" have VINs. "Ownership" is a relation
between cars and people.

So are you trying to model "sets" and their relations? What attributes
of sets that you include in your model depends somewhat on what you
plan to do with the data (what kinds of queries you are most
interested in).

HTH
ed

The theory group is usually pretty good for this stuff, too.

requiring a field [sic] of information to be related to it:

No, no; columns are not fields; these are tables and not files. With
a bad mind-set you forgot about what a row is and tried to build a
record. Records can have a variable numbers of fields, while rows
have a fixed number of columns.


CREATE TABLE MySets
(set_id INTEGER NOT NULL,
set_element CHAR(1) NOT NULL,
PRIMARY KEY (set_id, set_element));

Now when you need to pull out a permutation, you can use a query or a
VIEW

CREATE VIEW Floob (e1, e2, e2)
AS
SELECT S1.set_element, S2.set_element, S3.set_element
FROM MySets AS S1, MySets AS S2, MySets AS S3
WHERE S1.set_id = 1
AND S2.set_id = 2
AND S2.set_id = 3;

Obviously, you can do a COUNT(*) to get the size of each set or
permutation. And you can add more search conditions to forbid certain
values in the same permutation.