PostgreSQL SQL - Question re: relational technique

Author

Question re: relational technique

Robert Paulsen

2006-03-12, 11:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

chester c young

2006-03-12, 1:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Robert Paulsen

2006-03-12, 1:23 pm

On Sunday 12 March 2006 11:29, chester c young wrote:
> --- Robert Paulsen < robert@paulsenonline
.net> wrote:
>
> to
>
>
> do
>
>
> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Richard Huxton

2006-03-13, 7:25 am

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Robert Paulsen

2006-03-13, 7:25 am

On Monday 13 March 2006 03:03, Richard Huxton wrote:
> Robert Paulsen wrote:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links