Re: Attribute-values in separate table

anjasmedts_at_hotmail.com wrote:

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

Why not just have a "HasLock" relation with the id's of the furniture with locks? And a "HasWheels" relation with the id's of the furniture with wheels? Received on Thu Aug 30 2007 - 16:04:04 CEST