Обсуждение: Question re: relational technique

Here is a sample table:

item   item_id int   name    char   attrib1 char   attrib2 char   attrib3 char

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?

item   item_id int   name    char

details   item_id         int   attribute_name  char   attribute_value char

If this is a reasonable technique are their guidelines for its use? When is it
approptiate? When not?

Thanks,
Bob

--- 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; then write a view to cover it all; then write rules for
dml.  Sounds difficult but a cake walk once you've done it a few times.



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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.

Robert Paulsen wrote:
> 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.

The whole point of the database structure is to accurately reflect the 
requirements of your data. If you don't want your change your structure 
to keep track of the real world, why bother to structure it in the first 
place? Just stick it all in text documents and let htdig free-text 
search against it.

--   Richard Huxton  Archonet Ltd

On Monday 13 March 2006 03:03, Richard Huxton wrote:
> Robert Paulsen wrote:
> > 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.
>
> The whole point of the database structure is to accurately reflect the
> requirements of your data. If you don't want your change your structure
> to keep track of the real world, why bother to structure it in the first
> place? Just stick it all in text documents and let htdig free-text
> search against it.

Requirements change and differ from one application of the datbase to another. 
The database structure is maintained by others and is used by several diverse 
locations. It is an effort to incorporate and coordinate changes. The 
database already uses the name-value technique in one place, probably for 
this very reason. I was suspicious of the technique so posted my original 
question. The answer given is a resonable compromise. I can have my own table 
whose structure I control, even though the fields in the table "really" 
belong in another table.

Bob