Thread: 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