473,320 Members | 1,977 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Atrybutes ???

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

Nov 12 '05 #1
2 1829
""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

Nov 12 '05 #2
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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.