Multi-tenant Database Custom Schema Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-tenant database systems, adding custom data is challenging due to the inability to support online DDL in highly concurrent transactional systems, leading to schema overload and maintenance burdens, as well as unacceptable delays for other tenants when one organization creates schema.
Innovation Solution
The system allows multiple tenants to store custom fields and objects within a single data structure, using a single field for different data types and indexing columns, with globally unique primary keys, enabling flexible storage of variable schema data in a fixed physical schema.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional DDL is used to create custom schema in a multi-tenant database system, then each tenant can have custom data structures, but the underlying data dictionary catalog becomes overwhelmed and maintenance burden increases significantly
Solution Approach 1:
The patent segments the schema definition into two parts: a fixed physical data dictionary catalog that remains simple and manageable, and a flexible logical schema layer that allows each tenant to define custom fields and data structures. The physical catalog stores only the standardized table structures, while tenant-specific customizations are stored as separate configuration data or metadata, preventing catalog bloat while maintaining full customizability.
Solution Approach 2:
The patent introduces an intermediary layer between the physical database catalog and the tenant's custom schema requirements. This intermediary consists of configuration files, metadata tables, or a schema translation engine that maps tenant-specific custom fields to the fixed physical catalog structure, allowing custom schema flexibility without directly modifying or complicating the underlying data dictionary catalog.
2Productivity
If online DDL is used to allow concurrent schema creation, then tenants can modify schema without system downtime, but schema creation by one organization can lock the application for all other customers causing unacceptable delays
Solution Approach 1:
The patent segments schema modification operations into independent, non-blocking units. Instead of allowing full DDL operations that lock the entire catalog, the system allows tenants to define and register their custom schema elements as separate configuration entries that do not require exclusive access to the data dictionary catalog, enabling concurrent schema creation without application locking.
Solution Approach 2:
The patent performs preliminary schema validation and registration before actual data operations begin. Tenant customizations are pre-configured and validated against the fixed physical catalog structure, ensuring that subsequent data operations can proceed without locking delays. The schema registration process is designed to be lightweight and non-blocking, completing validation in advance of actual data access.
3Adaptability or versatility
If physical database schema is used for custom data storage, then data structure flexibility is achieved, but the fixed physical schema cannot accommodate variable schema data from multiple tenants
Solution Approach 1:
The patent resolves the contradiction by moving the variability dimension from the physical schema level to the logical/data level. The physical database catalog maintains a fixed, simple structure suitable for efficient storage and management. Tenant-specific schema variations are accommodated by adding a logical configuration layer that defines custom fields, data types, and relationships without altering the physical catalog structure, effectively adding a dimension of flexibility at the application layer.
Solution Approach 2:
The patent creates a universal fixed physical catalog structure that serves all tenants, while a configurable logical layer provides tenant-specific customization. The same physical catalog infrastructure supports multiple tenants with different schema requirements through configuration files or metadata, making the system universally applicable to diverse tenant needs without requiring separate physical schemas for each tenant.
Data Source
AI summary
Systems and methods for hosting variable schema data such as dynamic tables and columns in a fixed physical database schema. Standard objects, such as tables are provided for use by multiple tenants or organizations in a multi-tenant database system. Each organization may add or define custom fields for inclusion in a standard object. Custom fields for multiple tenants are stored in a single field within the object data structure, and this single field may contain different data types for each tenant. Indexing columns are also provided, wherein a tenant may designate a field for indexing. Data values for designated fields are copied to an index column, and each index column may include multiple data types. Each organization may also define custom objects including custom fields and indexing columns. Custom objects for multiple tenants are stored in a single custom object data structure. The primary key values for the single custom object table are globally unique, but also include an object-specific identifier which may be re-used among different entities.