Table Inheritance for Multi-Tenant Cloud Database Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing ERP applications in multi-tenant cloud environments face inefficiencies and high costs due to large amounts of base data storage, leading to hardware resource waste and increased costs, especially during customer onboarding, and existing copy-on-write arrangements result in significant data duplication and complexity.
Innovation Solution
Implementing a table inheritance arrangement where logical tables are represented by shared tables at common storage and tenant delta tables at individual tenancies, allowing queries to be handled transparently by the database management application, reducing redundant data storage and simplifying the ERP application layer.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If base data is stored separately at each tenancy, then data access speed is improved, but hardware resource consumption and costs increase significantly
Solution Approach 1:
The patent segments data into two types: shared base data stored at common storage, and tenant-specific delta data stored at individual tenancies. This segmentation allows read operations to access fast local delta tables while write operations only affect small delta tables, avoiding the need to replicate large base data across all tenancies.
Solution Approach 2:
Instead of copying entire base data tables to each tenancy, the patent creates lightweight delta tables that contain only tenant-specific modifications. These delta tables are merged with shared base data during query execution, providing fast local access without duplicating large amounts of base data.
2Adaptability or versatility
If copy-on-write arrangements are implemented, then data modification flexibility is improved, but data duplication and system complexity increase
Solution Approach 1:
The patent introduces delta tables as intermediary structures between shared base data and tenant-specific modifications. These delta tables serve as a mediator that captures all changes without requiring complex copy-on-write logic, simplifying the system while maintaining modification flexibility.
Solution Approach 2:
The patent implements dynamic table inheritance where the logical table structure adapts based on tenant needs. Delta tables are created dynamically when tenants modify data, and the system automatically manages the dynamic merging of base and delta data during query execution.
3Stability of the object's composition
If large amounts of base data are stored at common storage, then data consistency across tenants is improved, but query performance deteriorates due to data retrieval overhead
Solution Approach 1:
The patent performs preliminary actions by pre-loading shared base data into common storage and pre-creating delta table structures at each tenancy. This preliminary setup enables fast query execution by avoiding data retrieval overhead during actual queries, while maintaining consistency through the structured inheritance relationship.
Solution Approach 2:
The patent implements a nested table structure where delta tables are nested within the context of shared base data tables. This nesting allows the system to maintain a hierarchical relationship that ensures data consistency while enabling fast access to both shared and tenant-specific data through the nested structure.
Data Source
AI summary
Various examples are directed to systems and methods of managing a database in a multi-tenant cloud environment. A database management application may receive a first query directed to a first table. The first query may be received from a cloud application associated with a first tenant of the multi-tenant cloud environment. The database management application may access a first shared table associated with the first table and stored at a common storage of the multi-tenant cloud environment and a first tenant delta table associated with the first table and stored at a tenant data storage. The database management application may generate a first query response based at least in part on the first query, the first shared table and the first tenant delta table.