Re: Merging 2 "almost" identical databases.
Date: Mon, 29 Sep 2008 05:38:37 -0700
Message-ID: <1222691913.110778@bubbleator.drizzle.com>
Neomusashi wrote:
> Hi,
> So my work for the next few weeks is sorting out how to merge 2 almost
> identical databases used by 2 almost identical applications into one
> application with one database. These applications were developed for a
> client.
> Why 2 DB' s and 2 Applications? Well it’s a legacy decision. My
> predecessors decided it should be that way and now it ' s up to me to
> fix this. They needed small application/functional differences for
> other distribution channels and made a clone of the original app. .
> Now several years later they would like to merge the two apps into
> one, but of course they grew apart in functionality and structure. No
> big differences, but enough to make this a nice project ... .
> Short description:
> Each application runs on a sun solaris 10 server in a websphere 6.0
> application server (J2EE apps of course) and an orcale 10 database is
> used. Database queries are done using Stored Procedures. Both have 2
> schemas. One schema has the core data in about 20 tables and the other
> contains about 9 generic servers and about tables. The tables purposes
> don’ t really differ from each other, but columns, DDL and data load
> do.
>
> What I need to come up with is a merging process we can deliver to our
> client so they can easily merge the two databases. The process has
> two deliver a clear report.
> I was thinking of three possible ways to go:
> 1. Shell scripts with logging:
> pros: basic, quickest?
> Cons: Debugging, testing, complex, report/logging not so fancy/clear,
> inhouse expertise
> 2. Write a mini J2EE app:
> pros: easy to debug, easier to make?, better report/logging
> Cons: very slow to develop, work to throw away.
> 3. Write a java app:
> pros: easy to debug, easy to make, better report/logging
> cons: work to throw away, slow to develop
>
> I’ m feeling most for the 3th solution. How would you approach this?
I have a lot of questions from what you've written. Here they are in no particular order.
- After merger will it be necessary for the data to have some column indicating its origin to keep track of its historical origin?
- When you say "almost identical applications" are the changes to the application only or also to the schemas? If the schemas to column definitions such as names, data types, etc.?
- Is it possible that primary key and other unique constraints may be violated during a merger?
- Given that reality is that very few databases have clean data what are the chances of data in one or both of the schemas, at a customer site, need cleansing?
- Is there any chance that a customer has, on their own, made any changes to the definitions in any manner such as adding indexes or tables to support ad hoc reporting or internally developed functionality? -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org