Capturing and maintaining a timeline of data changes in a relational database system

A computerized method of capturing and maintaining a timeline of data changes in a relational database system, the method comprising:

• identifying, by a server computing device, a plurality of changed records from each of a plurality of relational database tables, each changed record comprising a primary key, a timestamp, and one or more changed data fields;
• analyzing, by the server computing device, the changed records to determine a minimum timestamp for each primary key;
• extracting, by the server computing device, the changed records associated with each primary key from each relational database table where the timestamp associated with each changed record is equal to or greater than the minimum timestamp for the primary key;
• generating, by the server computing device, one or more timestamp ranges for each primary key using the timestamps for the primary key in the captured records, each timestamp range comprising an effective date and an expiration date;
• determining, by the server computing device, whether each primary key-effective date combination already exists in a historical record table;
• for each primary key-effective date combination that already exists in the historical record table, update an existing record in the historical record table using the changed data fields from the changed record;
• and
• for each primary key-effective date combination that does not already exist in the historical record table, update an expiration date of an existing record in the historical record table using the effective date and insert a new record for the timestamp range, each new record comprising the primary key, the effective date, the expiration date, and the changed data fields from the changed record.

A computer program product for capturing and maintaining a timeline of data changes in a relational database system , the computer program product comprising a non-transitory computer-readable medium including instructions that, when executed by a server computing device, cause the server computing device to:

• identify a plurality of changed records from each of a plurality of relational database tables, each changed record comprising a primary key, a timestamp, and one or more changed data fields;
• analyze the changed records to determine a maximum timestamp for each primary key;
• extract the changed records associated with each primary key from each relational database table where the timestamp associated with each changed record is equal to or greater than the maximum timestamp for the primary key;
• generate one or more timestamp ranges for each primary key using the timestamps for the primary key in the captured records, each timestamp range comprising an effective date and an expiration date;
• determine whether each primary key-effective date combination already exists in a historical record table;
• for each primary key-effective date combination that already exists in the historical record table, update an existing record in the historical record table using the changed data fields from the changed record;
• and
• for each primary key-effective date combination that does not already exist in the historical record table, update an expiration date of an existing record in the historical record table using the effective date and insert a new record for the timestamp range, each new record comprising the primary key, the effective date, the expiration date, and the changed data fields from the changed record.