Method and apparatus for converting heterogeneous databases into standardized homogeneous databases

• US 9,552,380 B2
• Filed: 09/10/2013
• Issued: 01/24/2017
• Est. Priority Date: 07/10/2009
• Status: Active Grant

• Current Assignee: Robert Mack
• Original Assignee: Robert Mack
• Inventors: Mack, Robert
• Primary Examiner(s): Ahn, Sangwoo
• Application Number:
  US14/022,514
• Publication Number:
  US 20140012885A1
• Time in Patent Office: 1,232 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/211 Schema design and management
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

A method, an apparatus, and a system for configuring, designing, and/or implementing database tables are detailed that provides a framework into which a remainder of database tables is developed. Also detailed is a method to develop this framework of database tables. This so developed framework provides a platform for converting multiple independent heterogeneous databases into standardized homogeneous databases.

Claims

1. A method comprising

forming a first database in a computer memory, wherein the first database includes a first set of database tables and a first set of foreign key constraints;

wherein the first set of database tables has a first plurality of database columns;

adding a first unified boundary database table and a second set of foreign key constraints to the first database in the computer memory;

wherein the first unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the first unified boundary database table does not change any of the first set of foreign key constraints;

wherein the first unified boundary database table has a second plurality of database columns, which include one or more fundamental business key database columns;

wherein the first unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns;

wherein each of the one or more fundamental business key database columns is based upon a fundamental measurement or is based upon a data registry of reference data;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation;

wherein the data registry is a set of reference data stored in computer memory;

wherein the set of reference data includes a plurality of data records;

wherein the plurality of data records includes a plurality of data values, wherein one or more of the plurality of data values uniquely identifies each of the plurality of data records;

wherein each of the second set of foreign key constraints references a unique database index associated with the first unified boundary database table;

wherein the unique database index is based upon one or more of the second plurality of database columns of the first unified boundary database table;

wherein the unique database index is used to uniquely identify each data record of a plurality of data records stored in the first unified boundary database table;

wherein each of the foreign key constraints of the second set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables, so that a first plurality of foreign key database columns is instantiated by the second set of foreign key constraints; and

wherein the first plurality of foreign key database columns are duplicates of the one or more database columns that form the basis of the unique database index of the first unified boundary database table.

2. The method of claim 1 wherein

the first unified boundary database table is a dimensional database table such that the first unified boundary database table has multiple distinct levels of data record granularity.

3. A method comprising

forming a first database in a computer memory from a first repository of metadata in a computer memory;

wherein the first database includes a first set of database tables and a first set of foreign key constraints;

wherein the first set of database tables has a first plurality of database columns;

populating the first database with data records after the first database is formed;

forming a second database in a computer memory from a second repository of metadata in a computer memory, wherein the second database includes a second set of database tables and a second set of foreign key constraints;

wherein the second set of database tables has a second plurality of database columns;

wherein the second repository of metadata is different from the first repository of metadata;

populating the second database with data records after the second database is formed;

adding a first database table and a third set of foreign key constraints to the first database after the first database has been populated, and storing the first database table and the third set of foreign key constraints added to the first database in a computer memory, wherein the third set of foreign key constraints connect the first database table to one or more database tables in the first database; and

adding the first database table and a fourth set of foreign key constraints to the second database after the second database has been populated, and storing the first database table and the fourth set of foreign key constraints added to the second database in a computer memory, wherein the fourth set of foreign key constraints connect the first database table to one or more database tables in the second database;

populating the first database table in the first database from a first set of data records;

populating the first database table in the second database from the first set of data records;

adding a second database table and a fifth set of foreign key constraints to the first database in computer memory, and storing the second database table and the fifth set of foreign key constraints added to the first database in a computer memory, wherein the fifth set of foreign key constraints connect the second database table to one or more database tables in the first database;

adding a second database table and a sixth set of foreign key constraints to the second database in computer memory, and storing the second database table and the sixth set of foreign key constraints added to the first database in a computer memory, wherein the sixth set of foreign key constraints connect the second database table to one or more database tables in the second database;

populating the second database table in the first database from a second set of data records; and

populating the second database table in the second database from the second set of data records; and

wherein the first database table and the second database table are formed from a third repository of metadata that is different from the first repository of metadata and the second repository of metadata;

and further comprising

using a computer processor to add a first summary database table to the first database in computer memory;

wherein the first summary database table is comprised of a third plurality of database columns have their data values derived from the data values of the first plurality of database columns of the first database and a fourth plurality of database columns that are based on a first plurality of foreign key database columns of the first database and a second plurality of foreign key database columns of the first database;

wherein the metadata of the third plurality of database columns is formed from the third repository of metadata;

using a computer processor to add a seventh set of foreign key constraints to the first database in computer memory;

wherein one of the seventh set of foreign key constraints connects a first database table of the first database to the first summary database table of the first database;

wherein the first summary database table inherits the first plurality of foreign key database columns from the first database table of the first database, such that the first plurality of foreign key database columns are stored in the first summary database table of the first database in computer memory;

wherein the first plurality of foreign key database columns from the first database table of the first database are part of the fourth plurality of database columns of the first summary database table;

wherein a second one of the seventh set of foreign key constraints connects the second database table of the first database to the first summary database table;

wherein the first summary database table inherits the second plurality of foreign key database columns from the second database table of the first database;

and wherein the second plurality of foreign key database columns from the second database table of the first database are part of the fourth plurality of database columns for the first summary database table.

4. The method of claim 3 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.

5. The method of claim 3 wherein

the first database table is a unified boundary database table;

wherein the first unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the first unified boundary database table does not change any of the first set of foreign key constraints in the first database;

wherein the first unified boundary database table does not inherit any of the second plurality of database columns from any of the second set of database tables and adding the first unified boundary database table does not change any of the second set of foreign key constraints in the second database;

wherein the third plurality of database columns of the first unified boundary database table include one or more fundamental business key database columns;

wherein the first unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns;

wherein each of the one or more fundamental business key database columns is based upon a fundamental measurement or is based upon a data registry of reference data;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation;

wherein the data registry is a set of reference data stored in computer memory;

wherein the set of reference data includes a plurality of data records;

wherein the plurality of data records includes a plurality of data values, wherein one or more of the plurality of data values uniquely identifies each of the plurality of data records;

wherein each of the third set of foreign key constraints and the fourth set of foreign key constraints references a unique database index associated with the first unified boundary database table;

wherein the unique database index is based upon one or more of the third plurality of database columns of the first unified boundary database table;

wherein the unique database index is used to uniquely identify each data record of a plurality of data records stored in the first unified boundary database table;

wherein each of the foreign key constraints of the third set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables in the first database, so that a first plurality of foreign key database columns is instantiated by the third set of foreign key constraints;

wherein each of the foreign key constraints of the fourth set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the second set of database tables in the second database, so that a first plurality of foreign key database columns is instantiated by the fourth set of foreign key constraints; and

wherein the first plurality of foreign key database columns are duplicates of the one or more database columns that form the basis of the unique database index of the first unified boundary database table.

6. The method of claim 3 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.

7. A non-transitory computer readable medium comprising computer executable instructions which, when executed by a computer processor, perform the steps of:

forming a first database in a computer memory from a first repository of metadata in a computer memory;

wherein the first database includes a first set of database tables and a first set of foreign key constraints;

wherein the first set of database tables has a first plurality of database columns;

forming a second database in a computer memory from a second repository of metadata in a computer memory, wherein the second database includes a second set of database tables and a second set of foreign key constraints;

wherein the second set of database tables has a second plurality of database columns;

wherein the second repository of metadata is different from the first repository of metadata;

adding a first database table and a third set of foreign key constraints to the first database, and storing the first database table and the third set of foreign key constraints added to the first database in a computer memory, wherein the third set of foreign key constraints connect the first database table to one or more database tables in the first database; and

adding the first database table and a fourth set of foreign key constraints to the second database, and storing the first database table and the fourth set of foreign key constraints added to the second database in a computer memory, wherein the fourth set of foreign key constraints connect the first database table to one or more database tables in the second database;

populating the first database table in the first database from a first set of data records;

populating the first database table in the second database from the first set of data records;

adding a second database table and a fifth set of foreign key constraints to the first database in computer memory, and storing the second database table and the fifth set of foreign key constraints added to the first database in a computer memory, wherein the fifth set of foreign key constraints connect the second database table to one or more database tables in the first database;

adding a second database table and a sixth set of foreign key constraints to the second database in computer memory, and storing the second database table and the sixth set of foreign key constraints added to the first database in a computer memory, wherein the sixth set of foreign key constraints connect the second database table to one or more database tables in the second database;

populating the second database table in the first database from a second set of data records;

populating the second database table in the second database from the second set of data records; and

wherein the first database table and the second database table are formed from a third repository of metadata that is different from the first repository of metadata and the second repository of metadata; and

further comprising computer executable instructions which, when executed by a computer processor, perform the steps of;

adding a first summary database table to the first database in computer memory;

wherein the first summary database table is comprised of a third plurality of database columns have their data values derived from the data values of the first plurality of database columns of the first database and a fourth plurality of database columns that are based on a first plurality of foreign key database columns of the first database and a second plurality of foreign key database columns of the first database;

wherein the metadata of the third plurality of database columns is formed from the third repository of metadata;

and further comprising computer executable instructions which, when executed by a computer processor, perform the steps of adding a seventh set of foreign key constraints to the first database in computer memory;

wherein one of the seventh set of foreign key constraints connects a first database table of the first database to the first summary database table of the first database;

wherein the first summary database table inherits the first plurality of foreign key database columns from the first database table of the first database, such that the first plurality of foreign key database columns are stored in the first summary database table of the first database in computer memory;

wherein the first plurality of foreign key database columns from the first database table of the first database are part of the fourth plurality of database columns of the first summary database table;

wherein a second one of the seventh set of foreign key constraints connects the second database table of the first database to the first summary database table;

wherein the first summary database table inherits the second plurality of foreign key database columns from the second database table of the first database;

and wherein the second plurality of foreign key database columns from the second database table of the first database are part of the fourth plurality of database columns for the first summary database table.

8. The non-transitory computer readable medium of claim 7 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.

9. The non-transitory computer readable medium of claim 7 wherein

the first database table is a unified boundary database table;

wherein the first unified boundary database table does not inherit any of the first plurality of database columns from any of the first set of database tables and adding the first unified boundary database table does not change any of the first set of foreign key constraints in the first database;

wherein the first unified boundary database table does not inherit any of the second plurality of database columns from any of the second set of database tables and adding the first unified boundary database table does not change any of the second set of foreign key constraints in the second database;

wherein the third plurality of database columns of the first unified boundary database table include one or more fundamental business key database columns;

wherein the first unified boundary database table has a unique database index which is based upon one or more fundamental business key database columns;

wherein each of the one or more fundamental business key database columns is based upon a fundamental measurement or is based upon a data registry of reference data;

wherein a fundamental measurement is a measurement which is not derived from other measurements and which is determined directly from observation;

wherein the data registry is a set of reference data stored in computer memory;

wherein the set of reference data includes a plurality of data records;

wherein the plurality of data records includes a plurality of data values, wherein one or more of the plurality of data values uniquely identifies each of the plurality of data records;

wherein each of the third set of foreign key constraints and the fourth set of foreign key constraints references a unique database index associated with the first unified boundary database table;

wherein the unique database index is based upon one or more of the third plurality of database columns of the first unified boundary database table;

wherein the unique database index is used to uniquely identify each data record of a plurality of data records stored in the first unified boundary database table;

wherein each of the foreign key constraints of the third set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the first set of database tables in the first database, so that a first plurality of foreign key database columns is instantiated by the third set of foreign key constraints;

wherein each of the foreign key constraints of the fourth set of foreign key constraints is used by a computer processor to instantiate in computer memory one or more foreign key database columns into a single database table of the second set of database tables in the second database, so that a first plurality of foreign key database columns is instantiated by the fourth set of foreign key constraints; and

wherein the first plurality of foreign key database columns are duplicates of the one or more database columns that form the basis of the unique database index of the first unified boundary database table.

10. The non-transitory computer readable medium of claim 7 wherein

the first database table is a dimensional database table, such that the first database table has multiple distinct levels of data record granularity.