4

This question originates more from an "enterprise architecture" point of view. (Reason being, our company is just starting to get into data management, and actually having DBA's -- I know, horrific that it took so long to get to data management and having DBA's, but bear with me).

I'll lay some background first.

I am researching on how to move data from a legacy system to the new replacement system we are building. This data is more "lookup" data than what i would call "business", "customer", or "domain" data (if that makes sense). I guess the way I think of it is this is data that provides end-user's a choice and is then associated to the other types of data.

The challenge is that until we roll off our legacy systems we don't want to maintain the (legacy) data in two systems. We will need to maintain new data that can't work with the legacy system in the new system (so in that sense we will have dual maintenance). But the data that is common we want to initially push or pull from the legacy system.

This means some form of ETL, whether that be manually coded, or whether we use a product. (From a code perspective that means for us either RPG-since the legacy system is iSeries and DB2 for i-or Java-since the new system is Java EE-based with DB2 on AIX). From a product standpoint that probably means IBM InfoSphere DataStage (as that is product we have chosen to use for ETL for warehousing).

Now to the actual point of my question: The legacy system has data in non-normalized tables that rely on natural keys. The new system is in normalized tables that look more "object oriented" in their design (because of the heavy use of Hibernate). These tables rely more on the traditional integer-based primary key.

I am wondering, how does one best map from a system with natural keys to one with integer-based keys? How do you maintain this mapping so you know when to update records and when to insert new records into the integer-based system.

Any recommendations or best practices in general?

And then as an add on: Anything specific to RPG, Java, or InfoSphere DataStage?

Any ideas are appreciated, as these may be incorporated into our company's best practices we are attempting to build.

Improve this question

2 Answers 2

Reset to default
1

I have found building a relationship table between the old (natural) and new (integer) keys works well in conversion processes such as this. The integer key is built using the standard auto-increment method for the database. Maintain unique keys on the data which forms the natural key in the new tables.

Develop a repeatable data synchronization method that can be applied as often as required. Depending on your needs this can be daily, weekly, monthly, or on-demand. Make the synchronization modular so it can be updated as required. In some development processes you may end up feeding from the new system back to the old system.

As you develop and deploy the system, track which system is the system of record for which data. Move control to the new system as possible. You may want or need processes to identify and deal with changes which occur on the system which is not the system of record.

Manage change overs from the old system to the new. If possible, disable updates in the old system as the system of record is changed to the new system. This will require a feed from the new system to the old.

Improve this answer
1

I agree with above. If aside from the natural keys, nothing else changes and the integer could be replaced with the natural key on your new system, then make them unique to enforce the integrity of your data.

On the ETL side, when you do your initial load data, you will still need to do the lookup on the natural key BUT when you insert your row, you will use the looked up field (the integer key).

You might have to stage different loads. The look up tables first (those that are far from the center in the data model) and then the dependencies. At the end it can all be loaded at once with a giant work flow but personally I like to control every step of my process since it's a one time load anyway.

Good luck. This seems like a fun project.

I'm one of those DBAs. I haven't notice the disgusted look yet but the field is quite particular and we are often not understood from anyone else but other DBAs.

Improve this answer
2
  • Oops -- hope you didn't take my comment the wrong way! I edited my post to make more clear what I meant by "horrific" -- that I think we waited waaaayyyy too long to get to having data management and having DBA's. I wish we had DBA's sooner. And acutally I think DBA's are cool. I have contemplated a career switch from App Dev to DBA because I find databases fascinating. Commented Mar 30, 2011 at 12:57
  • It's ok. English is not my mother language. I probably miss translated it. Commented Mar 30, 2011 at 14:53

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.