COHERENT DATA IDENTIFICATION METHOD AND APPARATUS FOR DATABASE TABLE DEVELOPMENT

• US 20100174720A1
• Filed: 07/10/2009
• Published: 07/08/2010
• Est. Priority Date: 04/26/2006
• Status: Active Grant

• Current Assignee: Robert Mack
• Original Assignee: Robert Mack
• Inventors: Mack, Robert
• Granted Patent: US 7,979,475 B2
• Time in Patent Office: Days
• US Class Current: 707/741
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures

Abstract

A method, apparatus, and system for configuring, designing, and/or implementing database tables is detailed that provides a framework into which a remainder of database tables are developed. Also detailed is a method to develop this framework of database tables. This so developed framework provides a platform for integrating data from multiple databases. A method is also provided for maintaining and managing master data as a single source of reference data to multiple databases that are based upon this framework.

Claims

1. A method comprising the steps of

storing a plurality of sets of metadata in a computer memory using a computer program running on a computer processor;

and

storing a plurality of constituent data items in the computer memory using a computer program running on a computer processor, wherein each constituent data item of the plurality of constituent data items is comprised of one or more data items;

wherein each data item of the one or more data items of each of the plurality of constituent data items is a named data field which identifies a column in a database table in the computer memory;

wherein each of the plurality of sets of metadata corresponds to one of the plurality of constituent data items, such that there is one set of metadata for each of the plurality of constituent data items;

wherein each set of metadata includes

a constituent data item name for a corresponding one of the plurality of constituent data items;

an indication of which one or more interrogatives a corresponding one of the plurality of constituent data items may be assigned to,

at least one business key comprised of at least one business key data item,

and an indication of any dependencies of a corresponding one of the plurality of constituent data items on any other constituent data items of the plurality of constituent data items.

2. The method of claim 1 further comprising

examining each of the plurality of sets of metadata to determine if its corresponding one of the plurality of constituent data items is an independent constituent data item by use of a computer program running on the computer processor;

examining each business key data item of each of the plurality of sets of metadata to determine if each business key data item of each independent constituent data item is independent, wherein each business key data item of each independent constituent data item is independent if it is not dependent upon a business key data item in another constituent data item of the plurality of constituent data items;

wherein each constituent data item of the plurality of constituent data items is an independent constituent data item if;

(a) its corresponding set of metadata includes an indication that the corresponding constituent data item can only be assigned to one and only one interrogative, (b) it is assigned in the computer memory to one and only one interrogative and (c) it is not dependent upon any other constituent data item of the plurality of constituent data items for its definition; and

further comprising combining a first independent constituent data item of the plurality of constituent data items with a second independent constituent data item of the plurality of constituent data items, wherein the first and second independent constituent data items have the same one or more independent business key data items;

3. A method comprising

setting up and storing a first entity-relationship diagram in a first data model in a computer memory prior to storing data values in a first database in the computer memory;

using a computer processor to interpret the first entity-relationship diagram in the first data model in the computer memory in order to form the first database in the computer memory; and

storing a first plurality of data records in a database table, each including a row of a plurality of data values, in the first database;

wherein the first entity-relationship diagram includes;

a first unified reference data entity classified in the computer memory by one and only one first interrogative;

wherein the first unified reference data entity is an independent data entity in the first entity-relationship diagram;

further comprising adding the first unified reference data entity to a second entity-relationship diagram as an independent data entity;

wherein an independent data entity does not inherit any foreign key data attributes from any other data entity of the first data model, wherein a foreign key data attribute is a named data field which is used to identify a column of data values after the first database has been formed and wherein a foreign key data attribute is derived from a data entity; and

wherein a data entity is a component of the first entity-relationship diagram which becomes a database table once the first database is instantiated from the first entity-relationship diagram.

4. The method of claim 3 wherein

wherein the first unified reference data entity in the computer memory includes a first set of a plurality of elemental reference data items, wherein each elemental reference data item of the first set of the plurality of elemental reference data items, is classified by the first interrogative and only that first interrogative, and wherein setting up and storing the first entity-relationship diagram in the first data model in the computer memory includes storing the first set of the plurality of elemental reference data items in the first unified reference data entity in a first location in the computer memory;

wherein each elemental reference data item of the first set of the plurality of elemental reference data items is a named data field, wherein after the first database is formed each elemental reference data item of the first set of the plurality of elemental reference data items identifies a column in a first database table in the first database in the computer memory, so that there are a plurality of columns in the first database table, each of the plurality of columns identified by one corresponding elemental reference data item of the first set of the plurality of elemental reference data items;

wherein each elemental reference data item of the first set of the plurality of elemental reference data items aids in identifying a constituent data item, so that there is a first set of a plurality of constituent data items,

wherein each constituent data item of the first set of the plurality of constituent data items has a definition stored in the computer memory, so that there are a plurality of definitions stored in computer memory, one for each constituent data item of the first set of the plurality of constituent data items;

wherein each constituent data item of the first set of the plurality of constituent data items has a business key stored in the computer memory, so that there is a first set of a plurality of business keys, one for each constituent data item of the first set of the plurality of constituent data items;

wherein each business key of the first set of the plurality of business keys is comprised of one or more elemental reference data items;

wherein each business key of the first set of the plurality of business keys uniquely identifies its corresponding constituent data item; and

wherein each definition of each constituent data item of the first set of the plurality of constituent data items is independent of any other constituent data item of the first set of the plurality of constituent data items, such that each constituent data item of the first set of the plurality of constituent data items has a definition that does not refer to any other constituent data item of the first set of the plurality of constituent data items.

5. The method of claim 3 further comprising

storing a first universal surrogate primary key in the first unified reference data entity in the computer memory, wherein the first universal surrogate primary key is a named data field, wherein after the first database is formed the first universal surrogate primary key identifies a column of a plurality of data values in the database table in the first database in the computer memory;

wherein each data value of the column of a plurality of data values is different from every other data value of the column of a plurality of data values; and

wherein the first universal surrogate primary key is indicated in the computer memory to be the primary key to the first unified reference data entity as well as the primary key to each of a set of data entities that are related to the first unified reference data entity that result from a known data normalization technique of the first unified reference data entity; and

wherein each data value of the column identified by the first universal surrogate primary key uniquely identifies a data record of the unified reference data entity as well as the set of data entities that are related to the first unified reference data entity of the first database, that a plurality of data records are uniquely identified by a corresponding plurality of data values of the column of data values.

6. The method of claim 5

wherein after the first database is formed, the first universal surrogate primary key identifies a plurality of columns of data values in a corresponding plurality of database tables in the first database in the computer memory;

wherein each data value of the plurality of columns of data values is different from every other data value of the plurality of columns of data values.

7. The method of claim 3 further comprising

storing a first data context in the first unified reference data entity in the computer memory;

and

wherein the first data context is a named data field, wherein after the first database is formed the first data context identifies a column of data values in the database table in the first database in the computer memory;

wherein after the first database is formed and after the plurality of data values have been stored in the first database, the column in the database table identified by the first data context has at least two data values, each of which provides an indication of a type of data record stored in the database table of the first database.

8. The method of claim 7 further comprising

storing a first data context level in the first unified reference data entity in the computer memory;

and

wherein the first data context level is a named data field, wherein after the first database is formed the first data context level identifies a column of data values in the database table in the first database in the computer memory;

wherein after the first database is formed and after the plurality of data values have been stored in the first database, the column in the database table identified by the first data context level has at least two data values, each of which provides an indication a level of granularity of a data record to which each data value refers.

9. The method of claim 7 wherein

the type of data record may only be either a first type or a second type, which is different from the first type.

10. The method of claim 3 wherein

the first entity-relationship diagram includes:

a second unified reference data entity classified by one and only one second interrogative;

wherein the second unified reference data entity is an independent data entity in that first entity-relationship diagram;

wherein the second unified reference data entity is added to any entity-relationship diagram as an independent data entity;

wherein an independent data entity does not inherit any foreign key data attributes from any other data entity of the first data model, wherein a foreign key data attribute is a named data field which is used to identify a column of data values after the first database has been formed and which is derived from another data entity of the first data model.

11. The method of claim 10 wherein

wherein the second unified reference data entity includes a second set of the plurality of elemental reference data items, wherein each elemental reference data item of the second set of the plurality of elemental reference data items, relates to the second interrogative, and wherein setting up and storing the first entity-relationship diagram in the data model in the computer memory includes storing the second set of the plurality of elemental reference data items in the second unified reference data entity in a second location in the computer memory;

wherein each elemental reference data item of the second set of the plurality of elemental reference data items is a named data field, wherein after the first database is formed each elemental reference data item of the second set of the plurality of elemental reference data items identifies a column in a database table in the first database in the computer memory, so that there are a plurality of columns in the database table, each of the plurality of columns identified by one corresponding elemental reference data item of the second set of the plurality of elemental reference data items;

wherein each elemental reference data item of the second set of the plurality of elemental reference data items aids in identifying a constituent data item, so that there is a second set of the plurality of constituent data items;

wherein each constituent data item of the second set of the plurality of constituent data items has a definition stored in the computer memory, so that there are a plurality of definitions stored in computer memory, one for each constituent data item of the second set of the plurality of constituent data items;

wherein each constituent data item of the second set of the plurality of constituent data items has a business key stored in the computer memory, so that there is a second set of a plurality of business keys, one for each constituent data item of the second set of the plurality of constituent data items;

wherein each business key is comprised of one or more elemental reference data items;

wherein each business key of the second set of the plurality of business keys uniquely identifies its corresponding constituent data item; and

wherein each definition of each constituent data item of the second set of the plurality of constituent data items is independent of any other constituent data item of the second set of the plurality of constituent data items.

12. The method of claim 3 wherein

the first entity-relationship diagram includes:

a second unified reference data entity referring to a second interrogative;

a third unified reference data entity referring to a third interrogative;

a fourth unified reference data entity referring to a fourth interrogative;

a fifth unified reference data entity referring to a fifth interrogative; and

a sixth unified reference data entity referring to a sixth interrogative;

wherein the first, second, third, fourth, fifth, and sixth interrogatives are who, what, why, where, when, and how, respectively, and

wherein the second, third, fourth, fifth, and sixth unified reference data entities are independent data entities in the first entity-relationship diagram;

wherein the second, third, fourth, fifth, and sixth unified reference data entities are added to any entity-relationship diagram of the first data model in the computer memory as independent data entities;

wherein an independent data entity does not inherit any foreign key data attributes from any other data entity of the first data model, wherein a foreign key data attribute is a named data field which is used to identify a column of data values after the first database has been formed and which is derived from another data entity of the first data model.

13. The method of claim 12 wherein

the second, third, fourth, fifth, and sixth unified reference data entities include second, third, fourth, fifth, and sixth sets of the plurality of elemental reference data items, respectively, wherein each elemental reference data item of the second, third, fourth, fifth, and sixth sets of the plurality of elemental reference data items, relates to the second, third, fourth, fifth, and sixth interrogatives, respectively, and wherein setting up and storing the first entity-relationship diagram in the data model in the computer memory includes storing the second, third, fourth, fifth, and sixth sets of the plurality of elemental reference data items in the second, third, fourth, fifth, and sixth unified reference data entities in second, third, fourth, fifth, and sixth locations in the computer memory;

wherein each elemental reference data item of the second, third, fourth, fifth, and sixth sets of the plurality of elemental reference data items is a named data field, wherein after the first database is formed each elemental reference data item of the second, third, fourth, fifth, and sixth sets of the plurality of elemental reference data items identifies a column in a database table in the first database in the computer memory;

wherein each elemental reference data item of the second, third, fourth, fifth, and sixth sets of the plurality of elemental reference data items aids in identifying a constituent data item, so that there are second, third, fourth, fifth, and sixth sets of the plurality of constituent data items,

wherein each constituent data item of the second, third, fourth, fifth, and sixth sets of the plurality of constituent data items has a definition stored in the computer memory, so that there are a plurality of definitions stored in computer memory, one for each constituent data item of the second, third, fourth, fifth, and sixth sets, respectively, of the plurality of constituent data items;

wherein each constituent data item of the second, third, fourth, fifth, and sixth sets of the plurality of constituent data items has a business key stored in the computer memory, so that there are second, third, fourth, fifth, and sixth sets of a plurality of business keys, one for each constituent data item of the second, third, fourth, fifth, and sixth sets, respectively, of the plurality of constituent data items;

wherein each business key is comprised of one or more elemental reference data items;

wherein each business key of the second, third, fourth, fifth, and sixth sets of the plurality of business keys uniquely identifies its corresponding constituent data item; and

wherein each definition of each constituent data item of the second, third, fourth, fifth, and sixth sets of the plurality of constituent data items is independent, such that each definition does not refer to any other constituent data item of the first, second, third, fourth, fifth, and sixth sets, respectively, of the plurality of constituent data items.

15. The method of claim 3 further comprising

setting up and storing a second entity-relationship diagram in a second data model in a computer memory prior to storing data values in a second database in the computer memory;

using a computer processor to interpret the second entity-relationship diagram in the second data model in the computer memory in order to form the second database in the computer memory; and

storing a second plurality of data records, each including a second row of a plurality of data values, in the second database;

wherein the second entity-relationship diagram includes;

a second unified reference data entity referring to a second interrogative;

wherein the second unified reference data entity is an independent data entity in that second entity-relationship diagram;

wherein the second unified reference data entity may be added to any entity-relationship diagram as an independent data entity;

wherein an independent data entity does not inherit any foreign key data attributes from any other data entity of the first or the second entity-relationship diagrams, wherein a foreign key data attribute is a named data field which is used to identify a database table'"'"'s column of data values in the first database and the second database after the first and the second databases have been formed and that database table'"'"'s column of data values is derived from another database table'"'"'s column of data values in the first database or the second database.

16. The method of claim 15 wherein

the second unified reference data entity includes a second set of a plurality of elemental reference data items, wherein each elemental reference data item of the second set of the plurality of elemental reference data items, relates to the second interrogative, and wherein setting up and storing the second entity-relationship diagram in the second data model in the computer memory includes storing the second set of the plurality of elemental reference data items in the second unified reference data entity in a second location in the computer memory;

wherein each elemental reference data item of the second set of the plurality of elemental reference data items is a named data field, wherein after the second database is formed each elemental reference data item of the second set of the plurality of elemental reference data items identifies a column in a second database table in the second database in the computer memory, so that there are a plurality of columns in the second database table, each of the plurality of columns identified by one corresponding elemental reference data item of the second set of the plurality of elemental reference data items;

wherein each elemental reference data item of the second set of the plurality of elemental reference data items aids in identifying a constituent data item, so that there is a second set of a plurality of constituent data items;

wherein each constituent data item of the second set of the plurality of constituent data items has a definition stored in the computer memory, so that there are a plurality of definitions stored in the computer memory, one for each constituent data item of the second set of the plurality of constituent data items;

wherein each constituent data item of the second set of the plurality of constituent data items has a business key stored in the computer memory, so that there is a second set of a plurality of business keys, one for each constituent data item of the second set of the plurality of constituent data items;

wherein each business key is comprised of one or more elemental reference data items;

wherein each business key of the second set of the plurality of business keys uniquely identifies its corresponding constituent data item; and

wherein each definition of each constituent data item of the second set of the plurality of constituent data items is independent and consequently does not refer to any other constituent data item of the second set of the plurality of constituent data items;

and further comprising

combining the first database with the second database in the computer memory.

17. The method of claim 12 further comprising

finding a plurality of independent data entities among a plurality of data entities in a first existing entity-relationship diagram stored in the computer memory;

wherein the first existing entity-relationship diagram is a data model for a first existing instantiated database that has been populated with data records;

wherein each independent data entity of the plurality of independent data entities is a data entity of the plurality of data entities of the first existing entity-relationship diagram;

wherein each independent data entity is independent of every other data entity of the plurality of data entities of the first existing entity-relationship diagram, such that each independent data entity of the plurality of independent data entities does not inherit any foreign key data attributes from any other data entity of the plurality of data entities of the first existing entity-relationship diagram, wherein a foreign key data attribute is a named data field which is used to identify a database table'"'"'s column of data values after the first existing instantiated database has been formed and which is derived from another database table'"'"'s column of data values of the first existing instantiated database;

and further comprising adding the first existing entity-relationship diagram to the first entity-relationship diagram to form a first updated entity-relationship diagram by adding a plurality of data entity relationships between a plurality of the first, second, third, fourth, fifth, and sixth unified reference data entities of the first entity-relationship diagram and one or more of the plurality of independent data entities of the first existing entity-relationship diagram, to form the first updated entity-relationship diagram; and

wherein the added data entity relationships cause the originally independent data entities to become dependent data entities, wherein a dependent data entity inherits one or more foreign key data attributes from another data entity of the plurality of data entities of the first existing entity-relationship diagram or the first entity-relationship diagram; and

the first, second, third, forth, fifth, and sixth unified reference data entities are now the independent data entities of the first updated entity-relationship diagram.

18. The method of claim 12

further comprising adding a plurality of further data entities to the first entity-relationship diagram to form a modified entity-relationship diagram in the computer memory, wherein the step of forming the modified entity-relationship diagram includes forming a modified database;

wherein each of the plurality of further data entities have data entity relationships added making them dependent upon one or more of the first, second, third, fourth, fifth, and sixth unified reference data entities, wherein a dependent data entity inherits one or more foreign key data attributes from another data entity; and

further comprising populating a plurality of database tables of the modified database with data values in the computer memory;

wherein in a resulting modified entity-relationship diagram the first, second, third, fourth, fifth, and sixth unified reference data entities remain independent data entities.

19. The method of claim 17 wherein

the plurality of data entity relationships are based on one or more interrogatives which are associated in the computer memory with the plurality of independent data entities.

14. A method comprising

setting up and storing a first entity-relationship diagram in a first data model in a computer memory prior to storing data values in a first database in the computer memory;

setting up and storing a second entity-relationship diagram in a second data model in the computer memory prior to storing data values in the first database in the computer memory;

wherein the first entity-relationship diagram includes a set of independent data entities;

wherein the second entity-relationship diagram includes the set of independent data entities;

combining the first entity-relationship diagram and the second entity-relationship diagram to form a combined entity-relationship diagram in a combined data model in the computer memory;

using a computer processor to interpret the combined entity-relationship diagram in the combined data model in the computer memory in order to form the first database in the computer memory; and

storing a plurality of data records, each including a row of a plurality of data values, in the first database;

wherein each independent data entity of the set of independent data entities is a data entity of a data model of a plurality of data models of the first database that is independent of all other data entities of the plurality of data models of the first database, such that each independent data entity does not inherit any foreign key data attributes from any other data entity of any of the plurality of data models of the first database, wherein a foreign key data attribute is a named data field which is used to identify a database table'"'"'s column of data values after the first database has been formed and which is derived from another data entity of the plurality of data models of the first database;

wherein a data entity is database instantiated as a database table in the first database in computer memory.

20. A method comprising

storing a plurality of unified reference databases in one or more computer memories;

populating a plurality of data records for each of the plurality of unified reference databases in the one or more computer memories from a common source unified database;

wherein the common source unified database includes at least one of;

a who common source unified database, which includes only data records related to the interrogative who,

a what common source unified database, which includes only data records related to the interrogative what,

a where common source unified database, which includes only data records related to the interrogative where;

a when common source unified database, which includes only data records related to the interrogative when;

a how common source unified database, which includes only data records related to the interrogative how; and

a why common source unified database, which includes only data records related to the interrogative why.

21. The method of claim 20 wherein

wherein the common source unified database includes:

a who common source unified database, which includes only data records related to the interrogative who,

a what common source unified database, which includes only data records related to the interrogative what,

a where common source unified database, which includes only data records related to the interrogative where;

a when common source unified database, which includes only data records related to the interrogative when;

a how common source unified database, which includes only data records related to the interrogative how; and

a why common source unified database, which includes only data records related to the interrogative why.

22. A method comprising

storing a plurality of shared reference databases in a computer memory,

wherein each of the plurality of shared reference databases has a first plurality of shared reference database tables, such that the first plurality of shared reference database tables is contained within each of the plurality of shared reference databases;

wherein each of the first plurality of shared reference database tables has a business key index declared, so that there are a plurality of business key indices declared for the corresponding first plurality of shared reference database tables;

wherein each business key index of the plurality of business key indices is declared by Standard Query Language (SQL)) computer software command, such that there are a plurality of business key indices declared;

wherein each business key index of the plurality of business key indices is a named data field which will later identify a database table column of business key index data values in a database table in the computer memory;

wherein each business key index of the plurality of business key indices is comprised of at least one data field;

wherein each business key index is database instantiated from one or more business related data columns such that each of the business key index data values provide unique identification of a corresponding data record of a plurality of data records and the first business key index is database instantiated in each of the first plurality of shared reference database tables,

further comprising populating the plurality of data records for each shared reference database of the plurality of shared reference databases in the computer memory from a first common source database;

wherein each of the first plurality of shared reference database tables contains only a subset of all of the data records that are used in the plurality of shared reference databases;

wherein each of the first plurality of shared reference database tables is a data structure that is populated from a first common source database;

wherein each of the first business key index database table columns is independent of any other shared reference database table columns of the first plurality of shared reference database tables in that each of the first business key index database table column has all of its data records stored in the computer memory without referring to any other shared reference database table columns of the first plurality of shared reference database tables; and

further comprising using the first common source database to store and manage the data quality of a plurality of data records of each of the plurality of shared reference databases.

23. The method of claim 22 wherein

each of the plurality of shared reference databases is an ultimate elemental reference database, such that there are a plurality of ultimate elemental reference databases, wherein each ultimate elemental reference database of the plurality of ultimate elemental reference databases contains a plurality of ultimate elemental reference database tables;

wherein each of the plurality of ultimate elemental reference database tables of each of the plurality of ultimate elemental reference databases is independent of every other ultimate elemental reference database table of the plurality of ultimate elemental reference database tables of each of the plurality of ultimate elemental reference databases, such that each ultimate elemental reference database table contains the first business key unique index.

24. The method of claim 23 wherein

wherein the first common source database includes at least one of:

a who common source unified database, which includes only data records related to the interrogative who,

a what common source unified database, which includes only data records related to the interrogative what,

a where common source unified database, which includes only data records related to the interrogative where;

a when common source unified database, which includes only data records related to the interrogative when;

a how common source unified database, which includes only data records related to the interrogative how; and

a why common source unified database, which includes only data records related to the interrogative why; and

wherein the who common source unified database is used to populate the who ultimate elemental reference database tables in each of the who ultimate elemental reference databases;

wherein the what common source unified database is used to populate the what ultimate elemental reference database tables in each of the what ultimate elemental reference databases;

wherein the where common source unified database is used to populate the where ultimate elemental reference database tables in each of the where ultimate elemental reference databases;

wherein the when common source unified database is used to populate the when ultimate elemental reference database tables in each of the when ultimate elemental reference databases;

wherein the how common source unified database is used to populate the how ultimate elemental reference database tables in each of the how ultimate elemental reference databases; and

wherein the why common source unified database is used to populate the why ultimate elemental reference database tables in each of the why ultimate elemental reference databases.

25. The method of claim 23 wherein

wherein the first common source database includes:

a who common source unified database, which includes only data records related to the interrogative who,

a what common source unified database, which includes only data records related to the interrogative what,

a where common source unified database, which includes only data records related to the interrogative where;

a when common source unified database, which includes only data records related to the interrogative when;

a how common source unified database, which includes only data records related to the interrogative how; and

a why common source unified database, which includes only data records related to the interrogative why; and

wherein the who common source unified database is used to populate the who ultimate elemental reference database tables in each of the who ultimate elemental reference databases;

wherein the what common source unified database is used to populate the what ultimate elemental reference database tables in each of the what ultimate elemental reference databases;

wherein the where common source unified database is used to populate the where ultimate elemental reference database tables in each of the where ultimate elemental reference databases;

wherein the when common source unified database is used to populate the when ultimate elemental reference database tables in each of the when ultimate elemental reference databases;

wherein the how common source unified database is used to populate the how ultimate elemental reference database tables in each of the how ultimate elemental reference databases; and

wherein the why common source unified database is used to populate the why ultimate elemental reference database tables in each of the why ultimate elemental reference databases.