Message-ID: <4236BA96.4050701@archonet.com>
Date: Tue, 15 Mar 2005 10:36:06 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: U K Laxmi <laxmiuk@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: comparing 2 tables
References: <20050315101058.91004.qmail@web20025.mail.yahoo.com>
In-Reply-To: <20050315101058.91004.qmail@web20025.mail.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

U K Laxmi wrote:
> Thank you for the reply. Sorry for posting this
> problem here. Thought SQL syntax for Ms Access and
> PostgreSQL are similar. So, i posted here. 
> 
> My problem is:
> 
> I've original version of a table called PMM (Product
> Material Master). Thro' a web interface, user can
> change that table contents. Once changed i need to
> raise a ECN (Engineering CHange Note) specifying what
> changes happened to original PMM table whether rows
> are deleted, new rows are added or existing rows are
> modified etc. I've both old and new version of PMM
> tables.
> 
> The difference between two PMM tables are captured in
> a third table called ECN and it has both original &
> new PMM table entries which are not same.

Another option I've used in similar cases is to add a "version" column 
to the relevant tables (PMM in your case).

Users can only change rows with version="editing" (or "live" or other 
code) and after review can confirm their changes. This makes a copy of 
all the data but with a new version-code (usually auto-generated). You 
can now compare any two versions to track changes.

This system works well if you have relatively infrequent changes in 
large batches. In my case it was company-profile data (services, 
specialisations, contact personnel etc) and users would update their 
data at most every few months. I actually had a review phase in my 
system between editing and publishing a new version of a company's data.

--
   Richard Huxton
   Archonet Ltd