From: Robert Paulsen <robert@paulsenonline.net>
To: chester c young <chestercyoung@yahoo.com>
Subject: Re: Question re: relational technique
Date: Sun, 12 Mar 2006 12:15:45 -0600
User-Agent: KMail/1.9.1
Cc: pgsql-sql@postgresql.org
References: <20060312172903.6034.qmail@web54305.mail.yahoo.com>
In-Reply-To: <20060312172903.6034.qmail@web54305.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200603121215.46141.robert@paulsenonline.net>

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.