By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,834 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,834 IT Pros & Developers. It's quick & easy.

Atrybutes ???

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
""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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.