Re: Modelling items with attribute name/value pairs

I think storing data in this format is VERY useful. It makes sense for the problem.
The problem summarized is this: model items in a database which have a varying number of properties, where it is not possible to know ahead of time how many properties an item will have.

You could say it's similar to storing a record of events that occur for some entity, and it is not possible to know ahead of time what events will happen or when they will happen.

Mark, the alternative is to make columns in the main table for all possible attributes. For an indeterminate number of attributes, this just isn't possible.

This question pops up now and again on this newsgroup. For example, see the recent thread titled "Three table database - period (?)"

One thing to note: you'll probably run into the issue where "properties" have different datatypes. A suggestion I read on this newsgroup was to split up your "ItemProperties" table into separate tables for each datatype. - cb

Svenigalla <vu2syy_at_yahoo.com> wrote in message news:3 AB17592.1554B4AA_at_yahoo.com...
> 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 Sat Mar 17 2001 - 07:25:39 CET