Modelling items with attribute name/value pairs

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 Sat Feb 24 2001 - 02:29:01 CET