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

1. A system for capturing and maintaining a timeline of data changes in a relational database system, the system comprising a server computing device having a memory for storing computer-executable instructions and a processor that executes the computer-executable instructions 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 minimum 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 minimum timestamp for the primary key;

generate one or more timestamp ranges for each primary key using the timestamps for the primary key in the changed records, each timestamp range comprising an effective date and an expiration date;

determine whether each primary key-effective date combination of the timestamp ranges 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.

2. The system of claim 1 , wherein the plurality of relational database tables comprises a demographics table, an employment event table, and a life event table.

3. The system of claim 2 , wherein the server computing device loads the records from the historical record table into one or more database tables in a business analytics computing system.

4. The system of claim 1 , wherein generating one or more timestamp ranges for each primary key comprises:

determining each unique timestamp for the primary key in the changed records and arranging the unique timestamps in a temporal sequence;

generating a timestamp range for each unique timestamp, including:

assigning the unique timestamp as the effective date for the timestamp range,

for each unique timestamp other than the most recent unique timestamp, assigning a timestamp immediately before the next unique timestamp in the sequence as the expiration date for the corresponding timestamp range, and

for the most recent unique timestamp, assigning a default timestamp as the expiration date for the corresponding timestamp range.

5. The system of claim 4 , wherein the default timestamp is a null value or a distant future value.

6. The system of claim 1 , wherein identifying a plurality of changed records from each of a plurality of relational database tables comprises:

determining one or more data fields of interest in each relational database table; and

identifying the changed records from each relational database table where one or more of the data fields of interest has changed.

7. The system of claim 1 , wherein the server computing device:

receives a request for historical change data from a remote computing device, the request including a primary key and a timestamp range;

selects data records from the historical record table that match the primary key from the request;

filters the selected data records according to the timestamp range in the request; and

returns the filtered data records to the remote computing device in response to the request.

8. 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.

9. The method of claim 8 , wherein the plurality of relational database tables comprises a demographics table, an employment event table, and a life event table.

10. The method of claim 9 , wherein the server computing device loads the records from the historical record table into one or more database tables in a business analytics computing system.

11. The method of claim 8 , wherein generating one or more timestamp ranges for each primary key comprises:

determining each unique timestamp for the primary key in the changed records and arranging the unique timestamps in a temporal sequence;

generating a timestamp range for each unique timestamp, including:

assigning the unique timestamp as the effective date for the timestamp range,

for each unique timestamp other than the most recent unique timestamp, assigning a timestamp immediately before the next unique timestamp in the sequence as the expiration date for the corresponding timestamp range, and

for the most recent unique timestamp, assigning a default timestamp as the expiration date for the corresponding timestamp range.

12. The method of claim 11 , wherein the default timestamp is a null value or a distant future value.

13. The method of claim 8 , wherein identifying a plurality of changed records from each of a plurality of relational database tables comprises:

determining one or more data fields of interest in each relational database table; and

identifying the changed records from each relational database table where one or more of the data fields of interest has changed.

14. The method of claim 8 , wherein the server computing device:

receives a request for historical change data from a remote computing device, the request including a primary key and a timestamp range;

selects data records from the historical record table that match the primary key from the request;

filters the selected data records according to the timestamp range in the request; and

returns the filtered data records to the remote computing device in response to the request.

15. 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.