Time-span representation and time chain of events in a relational database

• US 7,603,396 B2
• Filed: 08/21/2006
• Issued: 10/13/2009
• Est. Priority Date: 08/19/2005
• Status: Active Grant

• Current Assignee: PrimePay LLC (PrimePay, Inc.)
• Original Assignee: SynchSource Incorporated
• Inventors: Durbin, Gary
• Primary Examiner(s): Robinson; Greta L
• Assistant Examiner(s): Wilcox; James J
• Application Number:
  US11/507,937
• Publication Number:
  US 20070050400A1
• Time in Patent Office: 1,149 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/2425 Iterative querying; Query formulation based on the results of a preceding query
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99936 Pattern matching access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability
• CPC Class Codes: Y10S 707/99955 Archiving or backup

Abstract

A method to produce a history of at least a portion of a database comprising: providing a data store that includes a record indicating a first event associated with first data that describes a subject matter, and indicating a first time interval during which the first data is effective with respect to the subject matter and indicating a second time interval during which the first data is current with respect to the database; and adjusting the data store upon an occurrence of a new event to, update a first record indicating the first event associated with first data and indicating an end time of the first time interval, and add a second new record indicating a second event associated with second data that describes the subject matter, and indicating a start of a new first time interval that is substantially continuous with an end time of the first time interval and during which the second data is effective with respect to the subject matter and indicating a start of a new second time interval during which the second data is current with respect to the database.

Claims

1. A computer-implemented method to produce a history recording changing events in a database comprising:

accessing with a computer processor a data store that includes a first record indicating first data associated with a first event, and the first record indicating a first effective time interval during which the first data is effective and the first record indicating a first time-of-creation time interval during which the first data is current with respect to the database, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp; and

adjusting the data store upon an occurrence of a second event, comprising;

adding a second record indicating second data associated with the second event, and the second record indicating a second effective time interval during which the second data is effective and the second record indicating a second time-of-creation time interval during which the second data is current with respect to the database, wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp,

if the first effective time interval starts earlier than the second effective time interval, updating the first effective time interval of the first record to be immediately preceding the second effective time interval of the second record, else setting the second effective time interval to be immediately preceding the first effective time interval.

5. The method of claim 1, wherein the first and second effective-dates record real world date-times of the first and second events giving rise to the first and second records.

6. The method of claim 1, wherein the first and second end-dates have initialized dates that are in the indefinite future.

7. The method of claim 1, wherein updating the first effective time interval to be immediately preceding the second effective time interval comprises:

updating the first end-date to be an instant prior to the second effective-date.

8. The method of claim 1, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

9. The method of claim 1, wherein the first and second create-timestamps record when the first and second records are first created.

10. The method of claim 1, wherein the first and second end-timestamps have initialized dates that are in the indefinite future.

11. The method of claim 1, further comprising:

if the first effective time interval starts earlier than the second effective time interval and if the data store further includes a third record indicating a third event associated with third data that describes the subject matter, and indicating a third effective time interval during which the third data is effective with respect to the subject matter and indicating a third time-of-creation time interval during which the third data is current with respect to the database, wherein the third effective time interval starts later than the second effective time interval, setting the second effective time interval to be immediately preceding the third effective time interval.

12. The method of claim 11, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the third effective time interval starts with a third effective-date and ends with a third end-date.

13. The method of claim 12, wherein the first, second, and third effective-dates record real world date-times of the first, second, and third events giving rise to the first, second, and third records.

14. The method of claim 12, wherein the first, second, and third end-dates have initialized dates that are in the indefinite future.

15. The method of claim 12, wherein updating the first effective time interval to be immediately preceding the second effective time interval comprises:

updating the first end-date to be an instant prior to the second effective-date.

16. The method of claim 12, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

17. The method of claim 12, wherein setting the second effective time interval to be immediately preceding the third effective time interval comprises:

setting the second end-date to be an instant prior to the third effective-date.

18. The method of claim 11, wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp, and wherein the third time-of-creation interval starts with a third create-timestamp and ends with a third end-timestamp.

19. The method of claim 18, wherein the first, second, and third create-timestamps record when the first, second, and third records are first created.

20. The method of claim 18, wherein the first, second, and third end-timestamps have initialized dates that are in the indefinite future.

2. An article of manufacture comprised of computer code stored on a computer readable memory to cause a computer system to perform a process to update a data store that includes a first record indicating first data associated with a first event, and the first record indicating a first effective time interval during which the first data is effective and the first record indicating a first time-of-creation time interval during which the first data is current with respect to a database, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp, the process comprising:

adjusting with a computer processor the data store upon an occurrence of a second event, comprising;

adding a second record indicating second data associated with the second event, and the second record indicating a second effective time interval during which the second data is effective and the second record indicating a second time-of-creation time interval during which the second data is current with respect to the database, wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp,

if the first effective time interval starts earlier than the second effective time interval, updating the first effective time interval of the first record to be immediately preceding the second effective time interval of the second record, else setting the second effective time interval to be immediately preceding the first effective time interval.

21. The article of manufacture of claim 2, wherein the first and second effective-dates record real world date-times of the first and second events giving rise to the first and second records.

22. The article of manufacture of claim 2, wherein the first and second end-dates have initialized dates that are in the indefinite future.

23. The article of manufacture of claim 2, wherein updating the first effective time interval to be immediately preceding the second effective time interval comprises:

updating the first end-date to be an instant prior to the second effective-date.

24. The article of manufacture of claim 2, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

25. The article of manufacture of claim 2, wherein the first and second create-timestamps record when the first and second records are first created.

26. The article of manufacture of claim 2, wherein the first and second end-timestamps have initialized dates that are in the indefinite future.

27. The article of manufacture of claim 2, further comprising:

if the first effective time interval starts earlier than the second effective time interval and if the data store further includes a third record indicating a third event associated with third data that describes the subject matter, and indicating a third effective time interval during which the third data is effective with respect to the subject matter and indicating a third time-of-creation time interval during which the third data is current with respect to the database, wherein the third effective time interval starts later than the second effective time interval, setting the second effective time interval to be immediately preceding the third effective time interval.

28. The article of manufacture of claim 27, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the third effective time interval starts with a third effective-date and ends with a third end-date.

29. The article of manufacture of claim 28, wherein the first, second, and third effective-dates record real world date-times of the first, second, and third events giving rise to the first, second, and third records.

30. The article of manufacture of claim 28, wherein the first, second, and third end-dates have initialized dates that are in the indefinite future.

31. The article of manufacture of claim 28, wherein updating the first effective time interval to be immediately preceding the second effective time interval comprises:

updating the first end-date to be an instant prior to the second effective-date.

32. The article of manufacture of claim 28, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

33. The article of manufacture of claim 28, wherein setting the second effective time interval to be immediately preceding the third effective time interval comprises:

setting the second end-date to be an instant prior to the third effective-date.

34. The article of manufacture of claim 27, wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp, and wherein the third time-of-creation interval starts with a third create-timestamp and ends with a third end-timestamp.

35. The article of manufacture of claim 34, wherein the first, second, and third create-timestamps record when the first, second, and third records are first created.

36. The article of manufacture of claim 34, wherein the first, second, and third end-timestamps have initialized dates that are in the indefinite future.

3. A computer-implemented method to produce a history recording changing events in a database comprising:

accessing with a computer processor a data store that includes a first record indicating first data associated with a first event, and the first record indicating a first effective time interval and the first record indicating a first time-of-creation time interval, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp; and

adjusting the data store upon an occurrence of a second event, comprising;

adding a second record indicating second data associated with the second event, and the second record indicating a second effective time interval and the second record indicating a second time-of-creation time interval, wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp,

if the first effective time interval starts earlier than the second effective time interval, updating the first time-of-creation interval of the first record and adding a third record indicating the first data associated with the first event, and the third record indicating a third effective time interval that is immediately preceding the second effective time interval of the second record and the third record indicating a third time-of-creation time interval, wherein the third effective time interval starts with a third effective-date and ends with a third end-date, and wherein the third time-of-creation interval starts with a third create-timestamp and ends with a third end-timestamp, else setting the second effective time interval of the second record to be immediately preceding the first effective time interval of the first record.

37. The method of claim 3, wherein the first, second, and third effective-dates record real world date-times of the first, second, and third events giving rise to the first, second, and third record.

38. The method of claim 3, wherein the first, second, and third end-dates have initialized dates that are in the indefinite future.

39. The method of claim 3, wherein indicating the third effective time interval that is immediately preceding the second effective time interval comprises:

indicating the third end-date to be an instant prior to the second effective-date.

40. The method of claim 3, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

41. The method of claim 3, wherein the first, second, and third create-timestamps record when the first, second, and third records are first created.

42. The method of claim 3, wherein the first, second, and third end-timestamps have initialized dates that are in the indefinite future.

43. The method of claim 3, wherein updating the first time-of-creation interval comprises updating the first create-timestamp to record when the second record is first created.

44. The method of claim 3, further comprising:

if the first effective time interval starts earlier than the second effective time interval and if the data store further includes a fourth record indicating a fourth event associated with fourth data that describes the subject matter, and indicating a fourth effective time interval and indicating a fourth time-of-creation time interval, wherein the fourth effective time interval starts later than the second effective time interval, setting the second effective time interval to be immediately preceding the fourth effective time interval.

45. The method of claim 44, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the third effective time interval starts with a third effective-date and ends with a third end-date, and wherein the fourth effective time interval starts with a fourth effective-date and ends with a fourth end-date.

46. The method of claim 45, wherein the first, second, third, and fourth effective-dates record real world date-times of the first, second, third, and fourth events giving rise to the first, second, third, and fourth record.

47. The method of claim 45, wherein the first, second, third, and fourth end-dates have initialized dates that are in the indefinite future.

48. The method of claim 45, wherein indicating the third effective time interval that is immediately preceding the second effective time interval comprises:

indicating the third end-date to be an instant prior to the second effective-date.

49. The method of claim 45, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

50. The method of claim 45, wherein setting the second effective time interval to be immediately preceding the fourth effective time interval comprises setting the second end-date to be an instant prior to the fourth effective-date.

51. The method of claim 44, wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp, and wherein the third time-of-creation interval starts with a third create-timestamp and ends with a third end-timestamp, and wherein the fourth time-of-creation interval starts with a fourth create-timestamp and ends with a fourth end-timestamp.

52. The method of claim 51, wherein the first, second, third, and fourth create-timestamps record when the first, second, third, and fourth records are first created.

53. The method of claim 51, wherein the first, second, third, and fourth end-timestamps have initialized dates that are in the indefinite future.

54. The method of claim 51, wherein updating the first time-of-creation interval comprises updating the first create-timestamp to record when the second record is first created.

4. A computer-implemented method to update a data store that includes a first record indicating first data associated with a first event, and the first record indicating a first effective time interval and the first record indicating a first time-of-creation time interval, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp, the method comprising:

adjusting with a computer processor the data store upon an occurrence of a second event, comprising;

adding a second record indicating second data associated with a second event, and the second record indicating a second effective time interval and the second record indicating a second time-of-creation time interval, wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp,

if the first effective time interval starts earlier than the second effective time interval, updating the first time-of-creation interval of the first record and adding a third record indicating the first data associated with the first event, and the third record indicating a third effective time interval that is immediately preceding the second effective time interval of the second record and the third record indicating a third time-of-creation time interval, wherein the third effective time interval starts with a third effective-date and ends with a third end-date, and wherein the third time-of-creation interval starts with a third create-timestamp and ends with a third end-timestamp, else setting the second effective time interval to be immediately preceding the first effective time interval.

55. The method of claim 4, wherein the first, second, and third effective-dates record real world date-times of the first, second, and third events giving rise to the first, second, and third record.

56. The method of claim 4, wherein the first, second, and third end-dates have initialized dates that are in the indefinite future.

57. The method of claim 4, wherein indicating the third effective time interval that is immediately preceding the second effective time interval comprises:

indicating the third end-date to be an instant prior to the second effective-date.

58. The method of claim 4, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

59. The method of claim 4, wherein the first, second, and third create-timestamps record when the first, second, and third records are first created.

60. The method of claim 4, wherein the first, second, and third end-timestamps have initialized dates that are in the indefinite future.

61. The method of claim 4, wherein updating the first time-of-creation interval comprises updating the first create-timestamp to record when the second record is first created.

62. The method of claim 4, further comprising:

if the first effective time interval starts earlier than the second effective time interval and if the data store further includes a fourth record indicating a fourth event associated with fourth data that describes the subject matter, and indicating a fourth effective time interval and indicating a fourth time-of-creation time interval, wherein the fourth effective time interval starts later than the second effective time interval, setting the second effective time interval to be immediately preceding the fourth effective time interval.

63. The method of claim 62, wherein the first effective time interval starts with a first effective-date and ends with a first end-date, and wherein the second effective time interval starts with a second effective-date and ends with a second end-date, and wherein the third effective time interval starts with a third effective-date and ends with a third end-date, and wherein the fourth effective time interval starts with a fourth effective-date and ends with a fourth end-date.

64. The method of claim 63, wherein the first, second, third, and fourth effective-dates record real world date-times of the first, second, third, and fourth events giving rise to the first, second, third, and fourth record.

65. The method of claim 63, wherein the first, second, third, and fourth end-dates have initialized dates that are in the indefinite future.

66. The method of claim 63, wherein indicating the third effective time interval that is immediately preceding the second effective time interval comprises:

indicating the third end-date to be an instant prior to the second effective-date.

67. The method of claim 63, wherein setting the second effective time interval to be immediately preceding the first effective time interval comprises:

setting the second end-date to be an instant prior to the first effective-date.

68. The method of claim 63, wherein setting the second effective time interval to be immediately preceding the fourth effective time interval comprises setting the second end-date to be an instant prior to the fourth effective-date.

69. The method of claim 62, wherein the first time-of-creation interval starts with a first create-timestamp and ends with a first end-timestamp, and wherein the second time-of-creation interval starts with a second create-timestamp and ends with a second end-timestamp, and wherein the third time-of-creation interval starts with a third create-timestamp and ends with a third end-timestamp, and wherein the fourth time-of-creation interval starts with a fourth create-timestamp and ends with a fourth end-timestamp.

70. The method of claim 69, wherein the first, second, third, and fourth create-timestamps record when the first, second, third, and fourth records are first created.

71. The method of claim 69, wherein the first, second, third, and fourth end-timestamps have initialized dates that are in the indefinite future.

72. The method of claim 69, wherein updating the first time-of-creation interval comprises updating the first create-timestamp to record when the second record is first created.