pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion re: relational technique
5+ messages / 3 participants
[nested] [flat]
* Question re: relational technique
@ 2006-03-12 17:11 Robert Paulsen <robert@paulsenonline.net>
0 siblings, 1 reply; 5+ messages in thread
From: Robert Paulsen @ 2006-03-12 17:11 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question re: relational technique
@ 2006-03-12 17:29 chester c young <chestercyoung@yahoo.com>
parent: Robert Paulsen <robert@paulsenonline.net>
0 siblings, 1 reply; 5+ messages in thread
From: chester c young @ 2006-03-12 17:29 UTC (permalink / raw)
To: Robert Paulsen <robert@paulsenonline.net>; pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question re: relational technique
@ 2006-03-12 18:15 Robert Paulsen <robert@paulsenonline.net>
parent: chester c young <chestercyoung@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Robert Paulsen @ 2006-03-12 18:15 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question re: relational technique
@ 2006-03-13 09:03 Richard Huxton <dev@archonet.com>
parent: Robert Paulsen <robert@paulsenonline.net>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2006-03-13 09:03 UTC (permalink / raw)
To: Robert Paulsen <robert@paulsenonline.net>; +Cc: chester c young <chestercyoung@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Question re: relational technique
@ 2006-03-13 12:15 Robert Paulsen <robert@paulsenonline.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Paulsen @ 2006-03-13 12:15 UTC (permalink / raw)
To: pgsql-sql; +Cc: Richard Huxton <dev@archonet.com>; chester c young <chestercyoung@yahoo.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-13 12:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-12 17:11 Question re: relational technique Robert Paulsen <robert@paulsenonline.net>
2006-03-12 17:29 ` chester c young <chestercyoung@yahoo.com>
2006-03-12 18:15 ` Robert Paulsen <robert@paulsenonline.net>
2006-03-13 09:03 ` Richard Huxton <dev@archonet.com>
2006-03-13 12:15 ` Robert Paulsen <robert@paulsenonline.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox