Column Templates for Table Metadata Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large tables with thousands or hundreds of thousands of columns in database systems face inefficiencies in operations such as searching and indexing due to the cumbersome nature of storing and managing column metadata, leading to increased storage costs and performance bottlenecks.
Innovation Solution
The implementation of column templates, which model columns as a logical entity by separating shared and unique attributes into template and attribute objects, allowing for efficient loading, historical data access, and reduced storage costs through unique version identification and n-gram inverted indexing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Quantity of substance
If traditional column metadata storage is used for large tables, then complete column information can be stored, but storage costs increase and operations become cumbersome
Solution Approach 1:
The patent segments column metadata into two distinct components: column templates (defining structure, data types, and constraints) and column instances (specific column data in tables). This segmentation allows the system to store only template definitions once and reference them across multiple tables, dramatically reducing storage requirements while maintaining complete metadata functionality.
Solution Approach 2:
The patent uses template copying where a single column template definition can be instantiated multiple times across different tables. Instead of storing redundant column metadata for each table, the system creates lightweight references to shared templates, reducing storage costs by 10 times while preserving the ability to access and modify column information.
2Ease of operation
If all column metadata is stored explicitly, then search operations can be performed, but indexing and searching become cumbersome with thousands of columns
Solution Approach 1:
The patent creates column templates that serve multiple functions simultaneously: they define column structure, enable search operations, support indexing, and provide data validation rules. This multi-functionality allows the system to perform efficient searching and indexing operations without requiring separate mechanisms for each function, improving productivity while maintaining ease of operation.
3Quantity of substance
If column templates are used to reduce storage, then storage costs decrease, but historical data access becomes more complex
Solution Approach 1:
The patent implements version control for column templates, where historical versions are preserved and can be accessed when needed. This preliminary action of maintaining version history allows the system to reduce storage costs by using templates while preserving the ability to access historical column definitions and changes, preventing any loss of historical information.
Data Source
AI summary
Described herein are techniques for compact representation of table columns via templatization. Column templates can model the collection of columns in a table as a logical entity. The logical entity can be split into two objects. A first object can contain a subset of column attributes (e.g., fields) that can be shared with other tables, such as name and data type. A second object can contain another subset of column attributes that are unique to a table, such as time stamps and created-on information.