Re: comparing 2 tables

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