Flexible Database Schema Dynamic Column Management

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional relational database systems return errors when queries or data manipulation instructions reference non-associated columns, limiting flexibility and user interaction, as they do not allow dynamic schema changes based on user requests.

Innovation Solution

Implementing a flexible schema in database tables that allows dynamic addition or deletion of columns in response to user instructions, enabling queries and data manipulation on non-associated columns by treating them as if they contain NULL values and updating the schema accordingly.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If a conventional relational database system enforces strict schema validation, then data integrity and reliability are maintained, but flexibility and ease of operation deteriorate because users cannot query or manipulate non-associated columns

Engineering Contradiction:

Improvedata integrityVSAvoiduser interaction flexibility

Core Design Contradiction:

ReliabilityVSEase of operation

Solution Approach 1:

The schema is transformed from a static structure to a dynamic one that can adapt during runtime. The system dynamically evaluates whether to create columns based on incoming queries, allowing the schema to evolve flexibly while maintaining data integrity through controlled validation rules.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system changes the parameter of schema rigidity to schema flexibility. By configuring the schema validation mode (strict vs. flexible), the system can switch between maintaining strict data integrity and allowing dynamic column creation, thus resolving the contradiction between reliability and ease of operation.

Inventive Principle:

Principle #35Parameter changes

2Adaptability or versatility

If a flexible schema allowing dynamic column addition is implemented, then adaptability and ease of operation improve, but system complexity and potential data integrity issues worsen

Engineering Contradiction:

Improveschema flexibilityVSAvoidschema management complexity

Core Design Contradiction:

Adaptability or versatilityVSDevice complexity

Solution Approach 1:

The system implements feedback mechanisms where the schema validation process monitors incoming queries and provides information about missing columns. Based on this feedback and configured policies, the system automatically creates appropriate columns, managing complexity through automated decision-making rather than manual schema administration.

Inventive Principle:

Principle #23Feedback

Solution Approach 2:

The database system performs self-service by automatically creating columns when it detects queries referencing non-associated columns. This self-adjusting behavior eliminates the need for manual schema modifications and reduces the complexity of schema management while maintaining high adaptability.

Inventive Principle:

Principle #25Self-service

3Productivity

If dynamic column creation is enabled, then user interaction and data handling capabilities improve, but processing overhead and system resource usage worsen

Engineering Contradiction:

Improvedata handling capabilityVSAvoidprocessing overhead

Core Design Contradiction:

ProductivityVSUse of energy by moving object

Solution Approach 1:

The system performs preliminary evaluation of incoming queries to determine if they reference non-associated columns before executing the query. By pre-assessing the query against the current schema and having predetermined policies for column creation, the system minimizes processing overhead during query execution while maintaining enhanced data handling capabilities.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS9031976B2Flexible tables

Publication Date: 2015.05.12 SAP SE

AI summary

A system includes reception of an instruction to access a column of a database table, determination that the column is not associated with the schema of the database table, determination of whether the schema of the database table is flexible or static, returning, if it is determined that the schema of the database table is static, of an error in response to the instruction, and, if it is determined that the schema of the database table is flexible, not returning an error in response to the instruction.