System for database creation, maintenance and access using event marking and two-dimensional partitioning

• US 6,401,098 B1
• Filed: 07/15/1999
• Issued: 06/04/2002
• Est. Priority Date: 07/15/1999
• Status: Expired due to Term

• Current Assignee: CGI Technologies and Solutions Incorporated (CGI Group)
• Original Assignee: American Management Systems, Incorporated (CGI, Inc.)
• Inventors: Moulin, Alexander
• Primary Examiner(s): Alam, Hosain T.
• Assistant Examiner(s): ALAM, SHAHID AL
• Application Number:
  US09/353,587
• Time in Patent Office: 1,055 Days
• Field of Search: 707/1-3, 707/9, 707/100-102, 707/500, 707/513, 707/200, 707/202, 705/1, 705/8, 705/9, 705/14, 705/26, 705/27, 705/2, 705/3, 705/4, 705/30, 705/34, 705/40, 713/200, 717/11, 709/202, 709/224, 711/112, 711/113
• US Class Current: 707/802
• CPC Class Codes: G06F 16/00 Information retrieval; Database structures therefor; File system structures therefor
• CPC Class Codes: G06Q 20/102 Bill distribution or payments
• CPC Class Codes: G06Q 30/04 Billing or invoicing
• CPC Class Codes: Y10S 707/957 Multidimensional
• CPC Class Codes: Y10S 707/968 Partitioning
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A system and method that reduces access, backup, restore and processing time required by partitioning data in a database into two dimensions. The first dimension is by event processing date and the second dimension is by partition group. Once partitioned in two dimensions the data is stored in two-dimensional partitions in the form of rolling tables including an event marker. This partitioning and marking is done upon receipt of data which eliminates the need for further processing to efficiently store data. This partitioning and marking further reduces the size of data blocks that need to be handled when backing up or restoring data, deleting data, and retrieving data. Thus, extremely large volumes of data can be handled in an efficient manner.

Claims

1. A method of two-dimensional database partitioning, comprising:

partitioning data received based on a first criteria;

partitioning the data partitioned based on a first criteria further according to a second criteria;

marking the data with an event marker, wherein the event marker represents an event sequence number, which is based on a date of billing for individual account; and

storing the data in a two-dimensional partition.

2. A method of two-dimensional database partitioning as recited in claim 1, wherein the first criteria is event processing date.

3. A method of two-dimensional database partitioning as recited in claim 2, wherein the second criteria is partition group.

4. A method of two-dimensional database partitioning, comprising:

partitioning data received based on a first criteria, wherein the first criteria is event-processing date;

partitioning the data partitioned based on a first criteria further according to a second criteria, wherein the second criteria is partition group;

marking the data with an event marker, wherein the event marker represents an event sequence number which is based on a date of billing for individual account; and

storing the data in a two-dimensional partition, wherein once the data is stored it is not further modified in normal operations unless circumstances demand otherwise.

5. A method of two-dimensional database partitioning, comprising:

partitioning data received based on a first criteria, wherein the first criteria is event processing date;

partitioning the data partitioned based on a first criteria further according to a second criteria, wherein the second criteria is partition group;

marking the data with an event marker, wherein the event marker represents an event sequence number which is based on a date of billing for individual account; and

storing the data in a two-dimensional partition, wherein once the data is stored it is not further modified in normal operations unless circumstances demand otherwise.

6. A method of two-dimensional database partitioning as recited in claim 5, wherein the date of billing for the individual account is based on a fixed billing cycle in which a particular customer account is billed on at the same time in a given time period.

7. A method of two-dimensional database partitioning as recited in claim 5, wherein the date of billing for the individual account is based on a threshold billing amount in which a particular customer account is billed when that customer account reaches a predetermined amount.

8. A method of two-dimensional database partitioning as recited in claim 5, wherein the date of billing for the individual account is based on a request received to generate the bill.

9. A method of two-dimensional database partitioning as recited in claim 5, further comprising:

extracting data for billing purposes based on the two-dimensional partition the data is stored in, the event marker and the Account ID.

10. A method of two-dimensional database partitioning as recited in claim 9, further comprising:

backing up data based on the two-dimensional partition the data is stored in.

11. A method of two-dimensional database partitioning as recited in claim 10, wherein data may be simultaneously entered into the two-dimensional partition while the backing up of data is occurring on two-dimensional partitions no longer accessed for storage of new data.

12. A method of two-dimensional database partitioning as recited in claim 10, further comprising:

deleting data, by eliminating the two-dimensional partition, based on the two-dimensional partition the data is stored in and the sequence number.

13. A method of two-dimensional database partitioning as recited in claim 12, wherein the event marker can be used for filtering to either enable or disable application processing of data in the two-dimensional partition.

14. A method of two-dimensional database partitioning, comprising:

creating a customer partition ID;

creating a customer partition;

collecting events, guide, rate, tax in the customer partition;

inserting the collected events, guide, rate, tax in the customer partition using an event sequence number;

determining whether to increment the customer partition table;

creating a new customer partition table when the determination is made to increment the customer partition table;

determining whether a current date is a cycle due date;

incrementing the cycle due date to a next month when a current date is a cycle due date;

incrementing the event sequence numbers for the Account IDs in that cycle;

determining whether the current date is a pricing due date; and

executing a billing run by extracting data having a given event sequence number from the customer partition tables.

15. A two-dimensional data structure embodied a computer readable medium for partitioning data in a manageable size comprising:

said two-dimensional data structure being partitioned based on a first criteria and further partitioned according to a second criteria and being stored in one of a plurality of two dimensional partitions with an event marker in order to keep the size of the two-dimensional partition to a manageable size, wherein the event marker represents an event sequence number which is based on a date of billing for individual account, whereby processing time to access, backup and delete data is significantly reduced.

16. A two-dimensional data structure embodied a computer readable medium as recited in claim 15, wherein the first criteria is event processing date.

17. A two-dimensional data structure embodied a computer readable medium as recited in:

claim 16, wherein the second criteria is partition group.

18. A two-dimensional data structure embodied a computer readable medium for partitioning data in a manageable size comprising:

said two-dimensional data structure being partitioned based on a first criteria and further partitioned according to a second criteria and being stored in one of a plurality of two-dimensional partitions with an event marker in order to keep the size of the two-dimensional partition to a manageable size, wherein the first criteria is event processing date, the second criteria is partition group, and once the data in the two-dimensional data structure is stored it is not further modified in normal operations unless circumstances demand otherwise, whereby processing time to access, backup and delete data is significantly reduced.

19. A two-dimensional data structure embodied a computer readable medium for partitioning data in a manageable size comprising:

said two-dimensional data structure being partitioned based on a first criteria and further partitioned according to a second criteria and being stored in one of a plurality of two-dimensional partitions with an event marker in order to keep the size of the two-dimensional partition to a manageable size, wherein the first criteria is event processing date, the second criteria is partition group, the event marker represents an event sequence number which is based on a date of billing for individual account, and once the data in the two-dimensional data structure is stored it is not further modified in normal operations unless circumstances demand otherwise, whereby processing time to access, backup and delete data is significantly reduced.

20. A two-dimensional data structure embodied a computer readable medium as recited in claim 19, wherein the date of billing for the individual account is based on a fixed billing cycle in which a particular customer account is billed on at the same time in a given time period.

21. A two-dimensional data structure embodied a computer readable medium as recited in claim 19, wherein the date of billing for the individual account is based on a threshold billing amount in which a particular customer account is billed when that customer account reaches a predetermined amount.

22. A two-dimensional data structure embodied a computer readable medium as recited in claim 19, wherein the date of billing for the individual account is based on a request received to generate the bill.

23. A two-dimensional data structure embodied a computer readable medium as recited in claim 19, further comprising:

data is extracted from the two dimensional data structure for billing purposes based on the two-dimensional partition the data is stored in, the event marker and the Account ID.

24. A two-dimensional data structure embodied a computer readable medium as recited in claim 23, further comprising:

the two-dimensional data structure is backed up data based on the two-dimensional partition the data is stored in.

25. A two-dimensional data structure embodied a computer readable medium as recited in claim 24, wherein data may be simultaneously entered into the two-dimensional partition while the backing up of two-dimensional partition is occurring on two-dimensional partitions no longer accessed to store new data.

26. A two-dimensional data structure embodied a computer readable medium as recited in claim 25, further comprising:

deleting data in the two-dimensional data structure, by eliminating the two-dimensional partition, based on the two-dimensional partition the data is stored in and the sequence number.

27. A two-dimensional data structure embodied a computer readable medium as recited in claim 26, wherein the event marker can be used for filtering to either enable or disable application processing of data in the two-dimensional partition.

28. A two-dimensional data structure embodied a computer readable medium for partitioning data in a manageable size comprising:

said two-dimensional data structure being partitioned based on a partition group and further partitioned according to an event processing date and being stored in one of a plurality of two dimensional partitions with an event marker in order to keep the size of the two-dimensional partition to a manageable size, and used to extract billing information for a customer using the event marker together with the Account ID from the plurality of two-dimensional partitions, wherein the event marker represents an event sequence number which is based on a date of billing for individual account and wherein the data is stored, retrieved and deleted based on the event marker and the two-dimensional partition the data is stored in.

29. A two-dimensional database system to partition data into manageable partitions for rapid access, backup and deletion, comprising:

a database divided into a plurality of customer partition, wherein the plurality of customer partition groups are formed in two dimensions;

an ERP event inserter to insert customer data tagged with an event marker into a customer partition of the plurality of customer partitions;

an ERP retriever to retrieve customer data from the plurality of customer partitions; and

a controller to control the formation of the plurality of customer partitions.

30. A two-dimensional database system as recited in claim 29, wherein the controller controls the formation of the plurality of customer partitions based on a first criteria and a second criteria.

31. A two-dimensional database system as recited in claim 30, wherein the first criteria is based on event processing date and the second criteria is based on partition groups.

32. A two-dimensional database system as recited in claim 31, wherein the ERP event inserter inserts customer data in the customer partitions when a customer transaction occurs.

33. A two-dimensional database system as recited in claim 32, wherein once the data is inserted in the customer partition by the ERP event inserter it is not further modified in normal operations unless circumstances demand otherwise.

34. A two-dimensional database system as recited in claim 33, wherein the ERP retriever retrieves customer data when a customer makes an inquiry or when a customer billing run is due.

35. A two-dimensional database system as recited in claim 34, wherein the event marker represents an event sequence number which is based on a date of billing for individual account.

36. A two-dimensional database system as recited in claim 35, wherein the date of billing for the individual account is based on a fixed billing cycle in which a particular customer account is billed on at the same time in a given time period.

37. A two-dimensional database system as recited in claim 35, wherein the date of billing for the individual account is based on a threshold billing amount in which a particular customer account is billed when that customer account reaches a predetermined amount.

38. A two-dimensional database system as recited in claim 35, wherein the date of billing for the individual account is based on a request received to generate the bill.

39. A two-dimensional database system as recited in claim 35, wherein the ERP retriever retrieves data for billing purposes based on the customer partition the data is stored in, the event marker and an Account ID.

40. A two-dimensional database system as recited in claim 39, wherein the controller backs up data based on the customer partition the data is stored in.

41. A two-dimensional database system as recited in claim 40, wherein data may be simultaneously entered by the ERP event insert into the customer partition while the backing up of data is occurring on two-dimensional partitions no longer accessed to store new data.

42. A two-dimensional database system as recited in claim 41, wherein the controller deletes data, by eliminating the customer partition, based on the customer partition the data is stored in and the sequence number.

43. A two-dimensional database system as recited in claim 42, wherein the event marker can be used for filtering to either enable or disable application processing of data in the customer partition.

44. A two-dimensional database system to partition data into manageable partitions for rapid access, backup and deletion, comprising:

a database divided into a plurality of customer partitions, wherein the customer partition groups have two dimensions based upon event processing date and partition group;

an ERP event inserter to insert customer data tagged with an event marker into a customer partition of the plurality of customer partition when a customer engages in a transaction;

an ERP retriever to retrieve customer data from the plurality of customer partitions when a customer inquirer occurs or when a customer bill must be generated or when the customer account reaches a predetermined amount; and

a controller to control the creation of the plurality of customer partition tables.