Date: Mon, 05 Dec 2005 15:06:58 -0400
From: Michael Burke <michael@engtech.ca>
Subject: Database with "override" tables
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Message-id: <43948FD2.3030904@engtech.ca>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20051007
	Debian/1.7.12-1

Hello,

I am in a situation where I have various tables (including data such as 
a product list) that are read-only to me.  I wish to provide the 
functionality of changing this table: Removing items, modifying items, 
creating new ones.  My original idea is to use a second table that is 
formatted similarly to the first, read-only table.

However, I can't just duplicate all the data and work in a separate 
table, as the original table may be modified by an outside source at any 
time.  Generally, I prefer to use the updated data from the read-only 
table, unless there has been reason to modify it in the past -- in which 
case the update may be safely ignored, and I would continue to use the 
second table.

The most effective set up I have come up with thus far is as follows:


CREATE TABLE initial_table (initial_id SERIAL PRIMARY KEY, desc TEXT);
CREATE TABLE override_table (override_id SERIAL PRIMARY KEY, initial_id 
INT, desc TEXT);


An entry in override_table with an initial_id matching an initial_id in 
initial_table would take precedence over the entry in initial_table.  A 
fairly simple SELECT statement can return the data I require:


SELECT COALESCE(ot.desc, it.desc) FROM initial_table it FULL JOIN 
override_table ot ON it.initial_id = ot.initial_id;


So far so good, but then when I need to reference data in those two 
tables from somewhere else, I need to reference both initial_id and 
override_id on the result set from that table.  I also end up using 
COALESCE an uncomfortable amount (though, if this is required I will do 
so).  I would prefer to treat the initial + override tables as a single 
SELECT set, but cannot come up with a way to do this cleanly, especially 
with regards to the IDs.

I am willing to abandon this format of "overriding" the initial table if 
it is way off-course, provided that the initial data be considered 
read-only and update-able in the absence of "override" data.

All suggestions are greatly appreciated!

Thanks in advance,
Mike.