On Wed, 2003-10-22 at 10:27, Grendel wrote:
> Hello !!!
> I have store some attrybutes to database. Attrybutes are set of integer values.
> {1,5,7} is valid set, but {1,3,1} is not valid set
>
> I have to build compare relation i.e.
> {1,5,7} is equivalent to {5,7,1} or {1,7,5} etc.
> but
> {1,5,7} is not equivalent to {5,1} or {1,4,2}
>
> What's best solution: arrays, master detail relation, something else...
I suggest that you make a separate relation with an int attribute and a
foreign key referencing the main relation. Then just make a unique index
on the combination of the foriegn key value and the integer value.
example:
CREATE TABLE main (
id int4 primary key,
attr2 type2,
attr3 type3
);
CREATE TABLE set_of_ints (
id int4 references main(id),
val int
);
CREATE UNIQUE INDEX set_uniq_idx ON set_of_ints(id,val);
Then you have the necessary constraints on data, and you can get the
sets in any order you like.
Arrays aren't the answer to this problem, because an array is not a set,
and arrays don't give you the benefits of relational operations.
Jeff