Database Triggers for Transitive Closure Maintenance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for calculating directed graph transitive closure in database systems have high time complexities due to the need for graph traversal upon invocation, making it inefficient to maintain hierarchical data in sync with objects modeled via the digraph.
Innovation Solution
The use of database triggers to perform delta operations on tables maintaining graph transitive closure and path structure, reducing the need for recalculations by firing triggers on vertex and arc additions or deletions, thereby dynamically updating the hierarchy bridge tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If graph traversal is performed upon invocation to calculate transitive closure, then the transitive closure is accurately computed, but the time complexity becomes high and performance deteriorates
Solution Approach 1:
The patent pre-calculates and stores the transitive closure in a dedicated table (TRANSITIVE_CLOSURE) before queries are executed. When vertices or arcs are modified, triggers automatically update the pre-computed closure data. This eliminates the need to perform graph traversal at query time, reducing time complexity from O(n³) or O(n⁴) to O(1) for retrieval operations.
Solution Approach 2:
The patent introduces an intermediary transitive closure table and trigger mechanisms that mediate between the base graph data and query operations. The triggers act as intermediaries that automatically maintain the closure table synchronously when the graph structure changes, separating the computation burden from query execution.
2Stability of the object's composition
If hierarchical data is maintained in sync with digraph objects using traditional methods, then data consistency is achieved, but the synchronization process requires recursive access and has high time complexity
Solution Approach 1:
The patent implements self-service synchronization through database triggers that automatically detect changes to the digraph structure and autonomously update the transitive closure table. The triggers fire events that cascade through the necessary updates without requiring external intervention or complex synchronization logic in application code, reducing both time complexity and operational complexity.
Solution Approach 2:
The trigger-based system establishes a feedback mechanism where changes to the graph structure automatically generate update events that propagate to the transitive closure table. This closed-loop feedback ensures data consistency by immediately responding to structural changes and maintaining the closure invariant without manual intervention.
3Reliability
If transitive closure is recalculated at request time, then the most current data is provided, but the retrieval operation has high time complexity and poor performance
Solution Approach 1:
The transitive closure is pre-computed and stored in a dedicated table before any queries are executed. Trigger mechanisms ensure that whenever the underlying graph structure changes (vertices added/deleted, arcs modified), the pre-computed closure data is automatically updated and maintained in sync. This allows queries to retrieve closure information in constant time O(1) while maintaining data currency through automatic synchronization.
Solution Approach 2:
The trigger-based maintenance system ensures continuous synchronization of the transitive closure table with the graph structure. Rather than recalculating at discrete query moments, the closure data is continuously maintained up-to-date through automatic trigger execution on every graph modification, ensuring both currency and performance.
Data Source
AI summary
Disclosed are methods and systems to provide for using database triggers to maintain a relational persistence of the transitive closure and path structure of an object hierarchy in the form of an object hierarchy bridge table. In one embodiment, database triggers fire when objects or relationships are added or deleted from the hierarchy. Based on the additions and deletions, a delta can be calculated and applied to an object hierarchy bridge table and the graph transitive closure and path structure can be dynamically built and maintained as corresponding changes to the graph occur. Later, more efficient access and retrieval of a graph transitive closure and path structure can be retrieved without necessarily having to perform recursion to calculate the graph transitive closure and path at request time.