Hierarchy Bridge Table Synchronous Update via Row-Level Triggers
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Navigating and maintaining consistency in large, complex hierarchy structures within data systems is inefficient due to recursive operations, which can lead to performance issues and inconsistencies in hierarchy bridge tables.
Innovation Solution
Implementing a system with row-level triggers that synchronously update the hierarchy bridge table during node insertions, deletions, and movements, using non-recursive operations to ensure the bridge table remains consistent with the hierarchy, thereby avoiding recursive updates and maintaining atomicity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If recursive operations are used to navigate hierarchy structures, then complete ancestor and descendent relationships can be identified, but system performance deteriorates and processing time increases
Solution Approach 1:
The patent pre-computes and stores all ancestor and descendent relationships in a bridge table before they are needed for queries. This preliminary action eliminates the need for recursive operations during actual navigation, as all relationships are already materialized and can be retrieved directly, thus resolving the contradiction between complete relationship identification and system performance
2Productivity
If a hierarchy bridge table is used to improve navigation performance, then query speed increases, but maintaining consistency with the hierarchy structure becomes complicated
Solution Approach 1:
The patent implements triggers that automatically detect changes to the hierarchy structure and provide feedback to the bridge table maintenance mechanism. This feedback ensures that the bridge table is updated synchronously with any hierarchy modifications, maintaining consistency automatically without requiring complex manual intervention, thus resolving the contradiction between fast navigation and consistency maintenance
3Reliability
If the hierarchy bridge table is updated synchronously during node insertions and deletions, then consistency is maintained, but update operations become more complex
Solution Approach 1:
The patent employs self-service mechanisms through automated triggers that monitor hierarchy changes and autonomously update the bridge table. This self-service approach maintains reliability by ensuring synchronous updates while reducing operational complexity, as the system automatically handles the synchronization without requiring complex external coordination or manual update procedures
Data Source
AI summary
A method for maintaining a hierarchy bridge table consistent with a corresponding hierarchy table includes listening for a transaction, having a start boundary and an end boundary, on the hierarchy table; and upon detecting and identifying the transaction, directing execution of an update method on the bridge table, where the update method is completed within the boundaries of the transaction using the identity of the transaction and a state of the bridge table at the start boundary of the transaction.