Re: Modelling items with attribute name/value pairs

Hi Mark,

In my view, storing data in this format is NOT of much help. By spreading the attributes of a single item in multiple records does not lend itself to be used in JOINS with other tables. Much worse, you dont even know what attribute each record holds!

So a better option is to store that data in a single varchar field, if your specific database allows. The data could be formatted using XML like this.

<item>
<attribute1>data1</attribute1>
..
</item>

This is no worse than storing the way you have initially thought of, from relational manipulation stand point. Atleast here, you are not wasting space and volume of records.

Cheers.

Mark wrote:

> Hi everyone,
>
> I am trying to model items in a database which have a varying number of
> properties. The idea would be to store item properties using attribute
> name/value pairs. Since it is not possible to know ahead of time how
> many properties an item will have, each property must be stored as a row
> in a table. Also, it is not possible to create a new property table for
> each item. As such, a table containing item properties would have to be
> shared by all items.
>
> Thus, the table containing item properties, say "ItemProperties", would
> look like this:
>
> ItemID AttributeName AttributeValue
> 1 AttrName1 AttrValue1
> 1 AttrName2 AttrName2
> 1 AttrName3 AttrName3
> 2 AttrName1 AttrValue1
> 3 AttrName1 AttrValue1
> 3 AttrName2 AttrValue2
> .. .. ..
>
> I have several concerns about such a design:
> - The properties of each item can be described by more than one row. (In
> fact, the properties are likely to be described by several rows at the
> very least). Thus, the ItemProperties table contains not one but several
> rows for each item.
> - In order to retrieve all the information associated with an item, all
> rows containing properties for the item must be retrieved from the
> ItemProperties table. This might impact negatively on performance.
> - As the number of items in the database grows, the number of rows in
> the ItemProperties table will grow to a much larger number.
>
> Are the above concerns valid for such a design? Is there a common or
> well known way for modeling a variable number of attribute name/value
> pairs in relational databases?
>
> Any comments or suggestions would be greatly appreciated.
>
> Thanks,
>
> Mark
> herc7899_at_my-deja.com
Received on Fri Mar 16 2001 - 03:06:14 CET