Re: Conversion of Location Codes with Foreign Keys

michael.dietz_at_gmx.de (Michael) wrote in message news:<e92fb27e.0404282210.1ae418d_at_posting.google.com>...
> I have to do a data migration for a client. The following conversion
> is required.
>
> Task:
> - MOVEMENT table with 7,000,000 records, each has a location code
> - Location code has changed; I have a mapping table with the old
> location code and the new code
>
> Issues:
> - The conversion should be done in the same table as I do not want to
> move the 7,000,000 records from one table to another if not necessary
> - The table has a foreign key to a LOCATION master; this means that I
> might have to update LOCATION first; however, I cannot just convert
> the codes in LOCATION as this would trigger foreign key violations in
> the MOVEMENT table; I also cannot just add the new locations because
> some new codes might be the same as existing old codes (e.g. the new
> code 'PDP' for 'Purchasing Department' could be the same as the old
> 'PDP' for 'Public Relations Department'.
>
> Does anybody know what the best approach would be here?
>
> Michael

Here are some thoughs. In theory there are 3 possibilites in the relationship of the old and new codes:
1- the old code and the new code are the same - nothing has to be done 2- the old code points to a new code that does not currently exist

    I would handle these first
3- the old code points to a new code but the new code is currently in use

   this will require first processing the data with the new code value

If you find an old code that points to a not currently existing new code and migrate it to the new code then the old code now becomes available for reuse and when you assign the data to this new value then this old value now becomes available for reuse.

This bring up another point for item 3 the old code may be on the list for reuse or it may not.

Using the information and logic above I would create a dependency chain where A goes into B into C into D to Z and then start processing at Z driving up through the chain in the reverse order. Each location freed is then available to the prior location.

Now because every location may not be reused, what you may actually have is a series of chains rather than one unbroken chain though by choosing one of the new not currently used values and processing the location that moves to it you can string the chains together to form one complete processing order.

Rather than try to build the chain on the fly I would write some plsql to build the chain and save it in a new column on the old to new table. I would also consider adding columns to indicate that the value has been processed for both building the chain and reassigning the data.

HTH -- Mark D Powell -- Received on Fri Apr 30 2004 - 03:19:15 CEST