Browse Prior Art Database

Method of Converting between Database Table Formats

IP.com Disclosure Number: IPCOM000016394D
Original Publication Date: 2002-Nov-18
Included in the Prior Art Database: 2003-Jun-21
Document File: 3 page(s) / 73K

Publishing Venue

IBM

Abstract

Disclosed is a method that makes it easy to convert relational database tables from existing (or legacy) formats to new formats. It is often used to migrate tables from legacy applications to new applications that have new and/or different table formats. As applications evolve and new applications are created a significant problem their developers face is supporting database tables that already exist (called legacy tables). Often these legacy tables do not contain all the desired information or are structured in an efficient way for the new applications use. Relational Database Systems (RDBMS) provide limited support to evolve tables, such as adding columns, changing column sizes or adding new tables. In many situations these types of changes are insufficient. For those insufficient cases developers are typically forced to create a whole new database structure and migrate the content from the legacy database into the new database. This disclosure describes a method to make such a migration simple to design and implement. The method is summarized as:

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 41% of the total text.

Page 1 of 3

Method of Converting between Database Table Formats

Disclosed is a method that makes it easy to convert relational database tables from existing (or legacy) formats to new formats. It is often used to migrate tables from legacy applications to new applications that have new and/or different table formats.

As applications evolve and new applications are created a significant problem their developers face is supporting database tables that already exist (called legacy tables). Often these legacy tables do not contain all the desired information or are structured in an efficient way for the new applications use. Relational Database Systems (RDBMS) provide limited support to evolve tables, such as adding columns, changing column sizes or adding new tables. In many situations these types of changes are insufficient.

For those insufficient cases developers are typically forced to create a whole new database structure and migrate the content from the legacy database into the new database. This disclosure describes a method to make such a migration simple to design and implement.

The method is summarized as:
1. read all legacy database table rows (across possibly several tables) related to one "object" into an in-memory tree structure
2. scan the tree from step 1 and build a similar tree that follows the new table structure building a new "object"; this can build multiple rows for step 3
3. write out the tree from step 2 into the new database tables
4. repeat steps 1 through 3 for all desired objects

All data transforms, including data type/value range changes are made in step 2. The mapping of one or more values from step 1 into values for step 3 is also done in step 2.

This approach offers the advantages that it is simple to understand and code to, reasonably efficient, and yet very powerful and general to all types of mapping data between database designs. This separation of work enables independent development of the three steps and also enables parallel (i.e., concurrent) execution (for improved performance) of these steps.

One major advantage of this approach is the use of an "object". The term object as used above means a set of tables related by foreign keys. It is common in database design to have a master (often called parent) table and several dependent (often called child) tables. Each child table is related to the parent table by the fact it has a foreign key that references the primary key of the parent table. Thus each child is related to the parent, as if it was a part of the parent. In most cases, the child records are deleted if the parent record is deleted. This child to parent relationship can cascade; that is a child table can be a parent to another table and so on as shown. All rows in each table related (directly or indirectly) by the same primary (for a parent) and foreign (for a child) key are considered part of the same object.

Because all records related to a single key are treated as a whole, this makes it extremel...