Method and system for storing shared data records in relational database
View Patent ↗Database management system comprises a hierarchical data tree. Each tree node is assigned a unique identifier used as a primary key record in each database table. A root of the tree hierarchy is a system data field created by a service provider. None of the individual subscriptions are assigned to the system data field, but all subscribers can read data from the tree branches located under this root. The data of individual subscriptions serves as branches of a hierarchical data tree. The branches are directly connected to the root data. For each subscription, data access and update policies are set for individual database tables and for individual table records. Each database table that stores shared data has a special data field with flags. The flags define read and modify policies for each. The flags are positions within a bit mask.
1. A computer-implemented hierarchical data tree management system, the system comprising:
a computer processor;
a memory coupled to the computer processor;
computer code loaded into the memory for implementing the following functionality:
a system data segment;
a plurality of subscriber data segments organized into a hierarchical tree, with the system data segment as the root node;
a shared data segment containing common subscriber data,
wherein:
the system data segment is created by a service provider and write operations into the system data segment are not allowed;
a copy of system data is provided on-demand to the subscriber data segments for modifications;
at least partial data from the subscriber data segments is provided to the shared data segment; and
wherein particular data from the system data segment becomes invisible after a first attempt of modification, and only common or subscriber data is available after copying from the system data segment; and
a table storing data of different subscribers, including system data, shared data, and subscriber data,
wherein the table includes a subscription id column that defines a data segment is used to determine to which data segment a database record belongs,
wherein the data segments are organized in a hierarchical data tree, and the hierarchical data tree is used to walk through the table for data retrieval and data modification, such that when a record is selected, key data and subscription id are passed to the table, and then a search is performed within the subscriber data segment, in any shared data segments and in the system data segment,
wherein a read flag is used to prevent returning duplicate records,
wherein only one of the records with identical key data that uniquely identifies a record contains a true value in the read flag,
wherein, to update a record in the table, all data segments in the hierarchy up to the root are searched by key data passed, and a write flag is analyzed, to see if an original record of the system data segment is modifiable,
wherein when the original record is not modifiable, the original record is copied into either a shared data segment lower in hierarchy for the subscriber who requested the modification, or, when there is no shared data segment with write operations allowed, into the subscriber data segment, and then a modification is performed for the copy of the original record, and the read flag of the original record is updated to make it invisible for a subscriber who has a copy of the original record.
2. The system of claim 1 , wherein the system data segment serves as a root of a hierarchical data tree.
3. The system of claim 1 , wherein the subscriber data segments serve as branches of a hierarchical data tree.
4. The system of claim 1 , wherein subscriber data access and data update policies are set for individual tables.
5. The system of claim 1 , wherein subscriber data access and data update policies are set for individual records within database tables.
6. The system of claim 1 , wherein read and write access to the shared data segment is defined by a set of flags.
7. The system of claim 6 , wherein the flags are implemented as positions within a bit mask.
8. The system of claim 7 , wherein a length of the bit mask equals to twice a number of the subscriber data segments.
9. The system of claim 7 , wherein a position of the flag is determined as a combination of a byte number within the bit mask and two-one-bit masks.
10. The system of claim 1 , wherein each of the subscriber data segments has a list of data records accessible within the shared data segment.
11. The system of claim 10 , wherein the list of accessible data records is created based on the hierarchical data tree.
12. A computer-implemented method for managing shared data, the method comprising:
generating a system data segment;
creating a plurality of subscriber data segments, the data segments are assigned to subscribers and organized into a hierarchical tree, with the system data segment as the root node;
creating a shared data segment containing common subscriber data, wherein:
the system data segment is created by a service provider and write operations into the system data segment are not allowed;
a copy of system data is provided to the subscriber data segments for modifications; and
at least partial data from the subscriber data segments is provided to the shared data segment,
wherein particular data from the system data segment becomes invisible after a first attempt of modification, and only common or subscriber data is available after copying from the system data segment;
assigning each of the subscribers a list of accessible data records within the shared data segment;
accessing the data from the shared data segment by verifying that a primary key value of a shared data field is included in the list of accessible data records; and
storing data of different subscribers in a table, including system data, shared data, and subscriber data,
wherein the table includes a subscription id column that defines a data segment is used to determine to which data segment a database record belongs,
wherein the data segments are organized in a hierarchical data tree, and the hierarchical data tree is used to walk through the table for data retrieval and data modification, such that when a record is selected, key data and subscription id are passed to the table, and then a search is performed within the subscriber data segment, in any shared data segments and in the system data segment,
wherein a read flag is used to prevent returning duplicate records, wherein only one of the records with identical key data that uniquely identifies a record contains a true value in the read flag,
wherein, to update a record in the table, all data segments in the hierarchy up to the root are searched by key data passed, and a write flag is analyzed, to see if an original record of the system data segment is modifiable,
wherein when the original record is not modifiable, the original record is copied into either a shared data segment lower in hierarchy for the subscriber who requested the modification, or, when there is no shared data segment with write operations allowed, into the subscriber data segment, and then a modification is performed for the copy of the original record, and the read flag of the original record is updated to make it invisible for a subscriber who has a copy of the original record.
13. The method of claim 12 , wherein the system data segment serves as a root of the hierarchical data tree.
14. The method of claim 12 , wherein the subscriber data segments serve as branches of a hierarchical data tree.
15. The method of claim 12 , wherein subscriber data access and data update policies are set for individual tables within the shared data segment.
16. The method of claim 12 , wherein subscriber data access and data update policies are set for individual records within database tables of the shared data segment.
17. The method of claim 12 , wherein ability of a standalone subscriber to insert data into the shared data segment is defined as a part of a database schema as a DEFAULT constraint.
18. The method of claim 17 , wherein a policy for insertion data into the shared data segment is handled on a level of a database server.
19. The method of claim 12 , wherein read and write access to the shared data segment is defined by a set of flags.
20. The method of claim 19 , wherein the flags are implemented as positions within a bit mask.
21. The method of claim 20 , wherein a length of the bit mask equals to twice a number of the subscriber data segments.
22. The method of claim 20 , wherein a position of the flag is determined as a combination of a byte number within the bit mask and two one-bite masks.
23. The method of claim 12 , wherein primary key values uniquely identifying a record at an application level are preserved for a copy of the record that is provided to the subscriber for modifications of the record.
24. The method of claim 23 , wherein primary key values are preserved for a record copy, including tables with SQL server autogenerated identity columns.