Attribute-values in separate table

From: <anjasmedts_at_hotmail.com>
Date: Thu, 30 Aug 2007 06:54:08 -0700
Message-ID: <1188482048.713274.53040_at_k79g2000hse.googlegroups.com>

Hi,

I have a to design a structure for a table with many attributes containing Yes of No values. The program I write, has to react in different ways corresponding the Yes/No-values in the different attributes.
For many records most of those values will be "No", as the program will perform an action only for a few records. This is all to avoid hard-coded checks.

The real situation is way too business related to explain, but as an example the following will do:
table:
  Furniture
attributes:
  ID
  Name
  HasLock
At the beginning, only a cupboard will have the attribute HasLock to Yes, all the other records will have this attribute to No. The program which uses these records will treat it in a way like: ... IF Haslock=yes THEN CreateLock()
In this way, it is very easy for the user to create a "Commode" and let the program create a lock for it, without having to change the program.
But only very few records will have the HasLock attribute to Yes. Furthermore, as many tests in the program depend on attributes of the furniture (HasWheels, NeedToBePaint, ...), the table will have a lot of attributes having Yes/No values. And every change request to my program will probably end up with the creation of an additional column in the table.

Now, I came up with an idea of setting up a table structure in two levels:
the first level containing a unique id and the name, and a second table with only those attributes which are "Yes": Table:
  Furniture
Attributes:
  ID
  Name

Table:
  Furniture-attribute
Attributes:
  Furniture-ID
  Attribute-Name

This will generate records in the Furniture table like:
( 123, "Cupboard")
( 456, "Chair")

and records in the Furniture-attribute table like:
( 123, "HasLock")
( 456, "HasWheels")

To be more flexible, I would extend the Furniture-attribute table like:
Table:
  Furniture-attribute
Attributes:

  Furniture-ID
  Attribute-Name
  Attribute-Type
  Attribute-Value

  Validity-begin-date
  Validity-end-date
...so I can "switch on and off" attributes on any date in the future.

What are the pro's and con's for such an approach?

Anja. Received on Thu Aug 30 2007 - 15:54:08 CEST