| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> comp.databases.theory -> Attribute-values in separate table
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
What are the pro's and con's for such an approach?
Anja. Received on Thu Aug 30 2007 - 08:54:08 CDT
 |
 |