Master Table Subscription Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional cloud-based platforms face inefficiencies in processing subscriptions for database updates, leading to unsustainable processing times due to the high volume of queries generated when handling a large number of subscriptions and database records.
Innovation Solution
A method and system utilizing a master table to consolidate subscription records and generate a mapping table, which maps fields of the subscription table to the master table, allowing for efficient querying and notification of subscribers upon database record updates, with the ability to automatically update the master table and mapping table, and notify subscribers via email or SMS based on matching conditions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional cloud-based platforms sequentially query each subscription against the underlying database record, then complete subscription processing is achieved, but processing time becomes unsustainable (1,150 days per day)
Solution Approach 1:
The patent pre-computes and stores SQL query results in a master table before they are needed. When a database record is created or updated, the system queries the pre-computed master table instead of executing sequential queries against all subscriptions. This preliminary action transforms the processing approach from reactive sequential querying to proactive pre-computation, reducing query time from 10ms per subscription to near-instantaneous table lookups.
Solution Approach 2:
The patent creates a copy of subscription data and query results in a master table structure. Instead of querying the original subscription records directly, the system queries the copied master table which contains pre-evaluated results. This copying approach allows the system to serve multiple query requests from the same pre-computed data, dramatically improving throughput while maintaining data consistency through periodic synchronization.
2Reliability
If the system processes 500,000 subscriptions with 20,000 database records created or updated per day, then complete notification coverage is achieved, but the system requires 10 billion queries per day which is unsustainable
Solution Approach 1:
The patent merges multiple subscription records and their associated SQL query results into a single consolidated master table. Instead of processing each subscription independently (10 billion separate queries), the system combines all subscription evaluations into one pre-computed master table. This merging reduces the operational burden from processing individual subscription queries to performing single table lookups, maintaining complete notification coverage while reducing daily query capacity requirements from 10 billion to a manageable level.
Solution Approach 2:
The system performs preliminary evaluation of all subscription conditions and stores the results in the master table before actual database events occur. This pre-computation allows the system to handle high volumes of database record creations and updates without requiring proportional query capacity, as the heavy lifting of condition evaluation is already completed in advance.
3Measurement precision
If glide SQL queries are stored as values within subscription records and triggered sequentially, then accurate condition matching is achieved, but query execution time adds up to 1,150 days per day
Solution Approach 1:
The patent pre-evaluates SQL queries against subscription conditions and stores the results in the master table before they are needed for actual notification processing. Instead of executing glide SQL queries sequentially when events occur, the system performs the query evaluation in advance and stores the boolean results. This maintains accurate condition matching while reducing execution time from 10ms per query to near-instantaneous table lookups.
Solution Approach 2:
The system creates a copy of the query evaluation results in the master table, storing pre-computed boolean values that represent whether each subscription condition is met. This copied data can be queried without re-executing the original SQL logic, ensuring measurement precision is maintained while eliminating repetitive query execution time.
Data Source
AI summary
A method for utilizing a master table is provided. The method includes: receiving a subscription table that includes at least one subscription record of at least one subscription that, upon satisfaction of at least one condition of the subscription(s), provides, to a subscriber, a notification of the at least one condition; generating the master table; and generating a mapping table that maps fields of the subscription table to fields of the master table. The master table consolidates each subscription record and stores, as a string value, each of the at least one condition.