From: "Lane Van Ingen" <lvaningen@esncc.com>
To: "Michael Burke" <michael@engtech.ca>
Cc: "PGSQL-SQL" <pgsql-sql@postgresql.org>
Subject: Re: Database with "override" tables
Date: Tue, 6 Dec 2005 10:10:55 -0500
Message-ID: <EKEMKEFLOMKDDLIALABIEENGCFAA.lvaningen@esncc.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <4395A650.9000607@engtech.ca>
Importance: Normal

Not quite sure how to answer this, but one thought does occur to me: I was
perhaps assuming that an override table would override an entire record in
the 'original' table(that is what we are doing), and we require that
critical fields in the override field be NOT NULL (and in some cases,
provide DEFAULT values). Will that help?

-----Original Message-----
From: Michael Burke [mailto:michael@engtech.ca]
Sent: Tuesday, December 06, 2005 9:55 AM
To: Lane Van Ingen
Cc: PGSQL-SQL
Subject: Re: [SQL] Database with "override" tables


Lane Van Ingen wrote:

>I think I have a similar situation involving the naming of assets, where
>the usual asset description is used, but users can enter a description in
>a separate table which 'overrides' the original name with a name that is
>more familiar to the individual.
>
>IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two
>select statements, like this:
>  select <override values> from foo1
>  union
>  select <normal values> from foo2
>    where <record not in foo1>;
>
>Hope this helps.
>
>
>
That almost works, and it is a much cleaner query than I had before.
However, there's a possibility that some columns in the overridden table
are NULL (indicating that the original value should be used).  So, a
particular asset may contain a description and price; the price may be
NULL, meaning the read-only value should be used, but the user may have
attached a special description as we previously outlined.

What I'm looking for is the ability to, perhaps, "overlay" foo2 onto
foo1, joined on foo1_id.  Then, NULL values in foo2 become "transparent"
and we see the foo1 values behind them.

Presently I am using COALESCE() for every pair individually, ie.
COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ...
and then doing a FULL JOIN.  This works.  I'm starting to wonder if it's
worth the extra hassle, I may just use your suggested UNION method instead.

Thanks again,
Mike.