You need to start this project with some research. And, one of the best technical means you can possibly get would be a sandbox where you can create databases from scratch and develop your technique without worries and waste of time.
It looks like your problem is called "welcome to the relational algebra word", sounds very usual. So, first of all you need to develop some mathematical model of mapping two databases with different database
schemas in a semantic way. First, try to determine where the information on the schema and semantics is stored. The semantics is only stored in a head of a developer (maybe accompanied with some documentation, and somehow reflected in semantic application code), but a
schema (metadata) is stored in a database itself. You can develop the code to retrieve it from the database, but it depends on the particular RDBMS.
Now, about the complexity of this problem itself. Did you ever heard of the "algorithm of the invention"? One of the approaches is: replace your problem, which seems to be too difficult, with the generalization of this problem. Naturally, if you solve a more general problem, it will automatically solve your current problem. But how it's possible that a more general problem, which is more general and by definition is at least as difficult as the particular one, can be easier for resolution? Wonderfully, there are so many cases when this approach helps. The difference is mental: looking at a particular problem clouds our vision by some particular detail which only look important but in fact are unrelated to the real problem and only distracts you from the real resolution. Your
Category_Table (using the word "Table" in the name of a table is ridiculous: who invented it?! This is the same as if Microsoft gave different names to executable files: instead of "WinWord.EXE" it would be "WinWord_Application", and, instead of "IExplore.EXE" it would be "IEApplication.exe" :-); in future, never use such names) and
Category_Types are just an example of cross-related tables.
It leads to another idea: try to abstract out from the semantics of the problem. If you did not know that the category tables contains "categories", what would you do? Probably, you would be able to formalize it, requiring some additional information, also abstracted from semantics: how some tables in old database is formally mapped onto some other tables in other tables of a new one?
And, finally, creation of a generalized problem, from the other hand, should lead to reduction of the size of the schema. This along can lead you to better understanding of how you can resolve the problem. Replace the model which currently involves tens (or hundreds?) of tables in each of the two databases, with more general one, which however operates with four tables in old database and five (or so) in the new one. Isolate principle complexity from complexity of big numbers, they are often unrelated.
One more "replacing" advice: mentally, replace your adjective "annoying" with "challenging". Consider every hassle as an opportunity.
—SA
Submit an article or tip