Re: building a row with a plpgsql function

Michael Kleiser wrote:
>
> Raphael Bauduin wrote:
>
>> Hi,
>>
>> We have a table collectiong all details of an item:
>> Here is a simplified representation of the tables
>> Tables:
>>
>> items
>> ------
>> item_id
>>
>>
>> item_details
>> ------------
>> item_detail_id
>> item_id
>> item_detail_name
>> item_detail_value
>>
>>
>> That way, we can easily have a variable number of detail for the items
>> (some can have 10 details, some only one).
>
>
> Be carefull: With this design, you can't declare any indexes
> and constraints on an item-detail !
> You also can't use different types for the item-details.
> Triggers would be more complicated.
>
> Consider to make the item-details to table-columns.
>

That was our first choice design, but we really need that extreme flexibility with the details.
Item details specs are a moving target, with impossibility to prevent changes.... Items are also very
different, but if we had to have one table per item, it would be unmanageable. New items types are
regularly created too, which would ask the creation of new tables.

About the different types we could have to store as details: we have a table with date details.

I'm also very cautious with that design, and am aware of the risks in it, but
we have thought about it a long time, and it seems to be the best in our situation.
We are in the final stage in this DB design and all tests until now have been positive.

Thanks for the warning anyway ;-)
I'm still open to an alternative solution, but we can't give up the flexibility this design gives us.

Raph