Modelling items with attribute name/value pairs

Modelling items with attribute name/value pairs

Post by Mark » Sun, 25 Feb 2001 10:29:01



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

 
 
 

Modelling items with attribute name/value pairs

Post by Jan Hidde » Sun, 25 Feb 2001 22:27:59



> 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'.

Quote:> 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

 
 
 

Modelling items with attribute name/value pairs

Post by Carlos Bromansk » Sun, 18 Mar 2001 15:25:39


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


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


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


 
 
 

Modelling items with attribute name/value pairs

Post by Svenigall » Sat, 17 Mar 2001 11:06:14


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.


> 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


 
 
 

1. name/value pairs in a table, need help with reporting techniques

Hi, I was wondering if someone could lend some experience or
assistance to this problem.

I have a table.

t_stuff
--------
stuff_id int,
stuff_name varchar(10),
stuff_propname varchar(10),
stuff_propvalue varchar(10)

There could be multiple rows for any given "stuff_name" that
correspond to the number of name/value pairs inserted into the table.
I'd like to be able to "turn the row on its side", basically be able
to (through either a view or stored procedure, which ever would be
faster) create a report that would set all of the "stuff_propname"
fields as columns, with the "stuff_propalue"'s displayed in the
corresponding row for that column.

some sample data to make this more clear:

stuff_id stuff_name stuff_propname  stuff_propvalue
-------- ---------- -------------- -----------------
1         apple       color           red
2         apple       taste           yummy
3         apple       origin          new york state
4         tomato      color           red
5         tomato      taste           hearty
6         tomato      origin          new jersey
7         tomato      seeds           true

report would look something like this

stuff_name  color  taste   origin          seeds
----------  -----  -----   ------          -----
apple       red    yummy   new york state  null
tomato      red    heary   new jersey      true

thanks for any and all help,
Ed

2. Help changing table (column size) definition

3. Extracting name value pairs from an XML string

4. See if you can solve this problem..

5. PL/SQL Code: Parsing Name Value Pairs.

6. ld.so.1: a.out: fatal: libixsql.so: can't open file:

7. Issue with SPs returning name-value pairs

8. Third-Level relationship Question

9. Combo-box drop-down + list-items-pairs in v9.1b

10. xsl:element | xsl:attribute - How do i dynamically assign values to the attributes before output

11. How to pivot a table of name/value pairs based on the lastest timestamp for a given pair?

12. Data Modelling & varying attributes