Thanks for the responses... i agree that it should be unique to attribute_id, not object_id... but the code listed above was pulled directly from the postgresql /packages/acs-kernel/sql/postgresql/acs-metadata-create.sql file - which came directly from the openacs4.5 tarball. To me it would have made more sense to have sort_order be a unique constraint in relation to the object_type - just as the two of you mentioned - hence the confusion. When pegged to attribute_id the constraint becomes irrelevent because you can't have two attributes of the same name for the same object.
You see this is what i don't understand, and this is more conceptual than anything else, it is why there isn't a generic way of storing data other than in text fields. I am trying to track user_profiles... and by this i mean home address, work address, phone numbers, birth date, number of children etc. you name it. This data is generic in nature (i.e. not dependent on a particular application), much like the biographies are part of generic acs_attributes and specific to a particular person, so it should be part of the object "person" (just as biographies are). So i was going to create a table like this...
create table userprofiles (
object_id integer not null
constraint acs_attr_values_obj_id_fk
references acs_objects (object_id) on delete cascade,
attribute_id integer not null
constraint acs_attr_values_attr_id_fk
references acs_attributes (attribute_id),
attr_value_boolean boolean,
attr_value_varchar200 varchar(200),
attr_value_varchar4000 varchar(4000),
attr_value_text text,
attr_value_date timestamp,
attr_value_numeric numeric,
constraint userprofiles_boolean_pk primary key
(object_id, attribute_id)
);
Which is essentially the same thing as the generic acs_attribute_values table... except that it allows me to choose the type of field i want to stuff the data in via the column_name field for that attribute, and create functions to enter it into the correct store.... what do you think? Would it make more sense to just base this information on the generic acs_attributes_values table and do an alter table to add the varchar, timestamp, integer and boolean columns?
