Re: Question re: relational technique

On Sunday 12 March 2006 11:29, chester c young wrote:
> --- Robert Paulsen <robert@paulsenonline.net> wrote:
> > One problem with the above is that the list of attributes is fixed. I
> > am looking for a way to assign new, previously undefined, attributes
>
> to
>
> > items without changing the table structure. Is it ever appropriate to
>
> do
>
> > the following?
> > ...
>
> There are two ways for extending tables, one static and one dynamic.
>
> Your scheme is dynamic.  You will have problems with typing and
> performance.  What you really want is to be able to list more
> attributes, similar to attributes attached to a tag in html.
>
> If you want a base table that has, under different specified
> conditions, extra attributes, there are better techniques.  IMHO the
> best solution is, for each condition, create a table containing the
> primary table's id plus the extra attributes; then join that to the
> base table

So, to be sure I understand, something like ...

item   item_id int   name    char

cond_one   cond_one_id     int   cond_one_descr  char   item_id         int   attribute_a     char   attribute_b
int

cond_two   cond_two_id      int   cond_two_descr   char   item_id          int   attribute_c      bool   attribute_d
 date
 

etc...

This still requires me to modify the overall database structure but not the 
original item table. As my reward :) I get to use any type I choose for each 
new attribute.