Database Custom Field Mapping via Position Indexing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in efficiently managing and accessing user-defined fields in existing tables without modifying the table structure, particularly in shared database environments where schema changes need to be propagated and user-defined field names must avoid conflicts, while maintaining query performance and handling sparse data effectively.
Innovation Solution
A system that maps user-defined fields to custom fields in a custom field table using a position-based indexing method, allowing user-defined fields to be stored and retrieved without altering the existing table structure, using techniques such as wide tables, vertical tables, or hybrid approaches to support flexible and efficient data storage and retrieval.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If user-defined fields are added directly to an existing table using ALTER TABLE, then query performance is improved and custom fields can be added as needed, but the table structure becomes complex and difficult to manage when multiple user-defined fields are added
Solution Approach 1:
The patent segments the storage of user-defined fields by creating a separate custom field table (CUSTOM_FIELD_TABLE) with columns for field_name, field_type, and field_value, rather than adding all custom fields directly to the main table. This segmentation allows the main table to remain simple while custom fields are stored independently, resolving the contradiction between query performance and table structure complexity.
Solution Approach 2:
The patent introduces a new dimension by creating a separate table structure for custom fields instead of extending the main table horizontally. The custom field table adds a new dimension of storage with columns like field_name, field_type, and field_value, allowing unlimited custom fields without increasing the complexity of the main table structure.
2Adaptability or versatility
If multiple users share a common schema in a hosted environment with ALTER TABLE, then schema changes can be propagated, but the tables become extremely wide with sparse data
Solution Approach 1:
The patent creates a universal custom field table structure that can serve multiple users and purposes. The CUSTOM_FIELD_TABLE with generic columns (field_name, field_type, field_value) can accommodate any user-defined field for any user, eliminating the need for each user to have their own wide table with sparse columns. This universal structure resolves the contradiction by providing adaptability without increasing table width or data sparseness.
3Device complexity
If user-defined fields are stored in a custom field table with mapping, then table structure simplicity is maintained and data sparseness is reduced, but query performance may be affected due to joins
Solution Approach 1:
The patent applies preliminary action by pre-computing and storing the mapping between user-defined fields and custom field table entries during data insertion or schema creation. The system pre-establishes the relationship between the main table rows and custom field table rows, so that during queries, the mapping is already available and does not require complex real-time computations, thus mitigating the performance impact of joins.
Data Source
AI summary
A system that performs a database operation which involves accessing user-defined fields which are stored in a custom field table, wherein the user-defined fields are used to augment an existing table. During operation, the system performs the database operation which involves accessing the user defined fields. While accessing the user-defined fields, the system performs a mapping between the user-defined fields and custom fields in the custom field table, wherein each row in the custom field table includes a position field and multiple custom fields.