Re: Modelling items with attribute name/value pairs

Mark wrote:
> 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.

The bottom line is the question how much disk space this is going to take extra and how much extra disk access this will cause. And that depends a bit on how much overhead is caused by every record in your DBMS. The best way to find out is just to experiment on your own system. (This may also depend upon the type of queries you will execute mostly.) Don't forget to cluster the table on 'itemID'.

> 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?

Well, basically that is what you already found. You can refine it somewhat by introducing different tables for different attribute types. So one table for all the integer attributes, one for the float attributes, one for the string attributes et cetera. If you don't do this all your values will have to be strings which is not an efficient way to store things.

You can also "tune" your design a bit by introducing two tables; one "classical" table with a primary key 'itemID' that contains the most frequent attributes (but with NULLs allowed), and a "flexible" table as you described above that contains the rare attributes and in the future the ones that you forgot to think of :-).

-- 
  Jan Hidders