""Grendel"" <po******@isukces.com> wrote in message
news:20*************************@jurajskie.pl...
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...
Thnaks in advance
Grendel
___________________________________
NOCC, http://nocc.sourceforge.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Master-detail is the option...
I dont think you can create an index or unique constraint on elements of an
array {data type integer[]}. I've never tried it though so I could be
wrong...
..My suggestion -- It sounds like you might need a parent-child set of tables
rather than storing your attributes in an array in the parent table...
Basically -- the id of the parent table is a foreign key in the child table
and you have a unique key on the child table that consists of the foreign
key and the attribure (parent_id, attribute)
So that 'parent.id' maps into 'child.parent_id' and the UNIQUE
('child.parent_id', 'child.attribute') implement the uniqueness of the
elements in your set (array)...,
This will pretty much guarantee that attributes of the parent are unique
sort of like this:
parent table...
CREATE TABLE parent (
id int4 NOT NULL DEFAULT nextval('p_parent_seq'::text),
cctype_desc varchar(72),
active_flag bool DEFAULT 'T',
create_dt timestamp DEFAULT 'NOW()',
change_dt timestamp,
change_id int4 DEFAULT 0,
CONSTRAINT p_parent_id_key PRIMARY KEY (id)
) WITH OIDS;child table:
child table:
CREATE TABLE child (
id int4 NOT NULL DEFAULT nextval('p_child_seq'::text),
parent_id integer not null,
attribute integer not null,
CONSTRAINT p_child_id_key PRIMARY KEY (parent_id, attribute)
) WITH OIDS;child table:
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762