Re: Any objections to including the database schema unique id patch?

[David Tangye <..hidden..>]

On Tue, 2006-09-12 at 23:08 -0700, Chris Travers wrote:

> Note though that these are *serious* problems that would pre-date the
> migration and would certainly need to be dealt with sooner than later.

OK now I remember how this was always handled, and now I can advise...

1. Firstly set up the LedgerSMB schema to ensure data integrity is
guaranteed, eg NOT NULLS, PK constraints, correct sequences
2. Any sort of robust useful migration eg from ProductX into eg
LedgerSMB has to be a two step process as follows:

Step 1 is a process that merges and splits etc source records and
inserts rows into migration tables. The migration tables essentially
image the LedgerSMB schema, but the above constraints are relaxed/not
present.

Step 2 is to run checks to ensure that data passes the rules that the
constraints would enforce. Errors are printed and logically correct sets
of data are moved into the production master LedgeSMB tables ie insert
related rows there and delete from the migration set in a single commit
interval. What is left in the migration tables are rows that need
DBAs/techos to hack into shape, one way or another before attempting
step 2 again. Steps one and two are runnable by any user.

This might sound complex, but 
 - its the only tried and true way to get useful data into a robust data
structure and ensure ongoing system/data integrity
 - its not that hard once its set up
 - it allows all users to try the new system with confidence
 - it is good advertising as to the robustness of your system if you are
pointing out and fixing data errors that other systems have let creep
into there's.

Data migration can take up to about a third the effort of building a
system. Many people allow more like between zero and one tenth, and get
badly bitten as a result.