Bulk Loading Hierarchical Relational Data Using Pseudo-Key Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies when writing hierarchical data, requiring recursive queries to determine parent-child relationships, leading to excessive network and database resource usage due to the need to know parent keys in advance for child record insertion.
Innovation Solution
A computing system that uses pseudo-keys to organize and write relational data in bulk, generating real keys for each level of the hierarchy without needing to know internal database keys upfront, allowing for efficient bulk loading across unlimited levels of relationships.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If hierarchical data is written to the database using traditional methods with parent keys known in advance, then data integrity and relationships are maintained, but the number of database calls increases significantly (1110 calls for 3 levels with 10 children each)
Solution Approach 1:
The patent applies preliminary action by first writing all parent records to the database before writing child records. This ensures that parent keys are available before child records need them, maintaining data integrity while enabling bulk loading. The system performs the parent writing operation in advance, storing results in a key map for subsequent child record processing.
Solution Approach 2:
The patent introduces a key map as an intermediary data structure that stores the mapping between written parent records and their generated database keys. This intermediary allows child records to reference parent keys without requiring real-time database queries, reducing the number of database calls while maintaining relationship integrity.
2Reliability
If parent keys are queried from the database before writing child records, then correct parent-child relationships are established, but network and database resources are used inefficiently
Solution Approach 1:
The system performs preliminary writing of all parent records before processing child records, eliminating the need for intermediate database queries to obtain parent keys. This preliminary action stores all necessary key information in memory via the key map, reducing network and database resource consumption while ensuring accurate parent-child relationships.
Solution Approach 2:
The patent creates a copy of parent key information in the key map data structure, allowing child records to access parent keys from this in-memory copy rather than querying the database repeatedly. This copying mechanism maintains relationship accuracy while significantly reducing database and network resource usage.
3Productivity
If bulk loading is performed without knowing internal database keys in advance, then resource efficiency is improved, but the complexity of managing hierarchical relationships increases
Solution Approach 1:
The key map serves as an intermediary that manages the complexity of hierarchical key relationships. It automatically stores and retrieves the mapping between written records and their database-generated keys, allowing bulk loading to proceed efficiently without requiring application-level key management logic. This intermediary absorbs the complexity, presenting a simple interface to the rest of the system.
Data Source
AI summary
A computing system includes a processor, a program memory to store an application, and a database. The processor executes the application to store objects in the program memory, with the objects being organized in a relational hierarchy having a plurality of levels, with each object having a respective pseudo-key associated therewith. The processor writes the objects and their respective pseudo-keys in a first level of the relational hierarchy to the database to generate the first level of the relational hierarchy with real keys. The processor receives a first level key map mapping the pseudo-keys with the real keys for the objects in the first level of the relational hierarchy, reconciles the pseudo-keys for the objects in a second level of the relational hierarchy with the first level key map, and writes the objects and their respective reconciled pseudo-keys in the second level of the relational hierarchy to the database.