Automated Database Modeling via Dynamic Normalization

Solution Overview

Problem

Computing systems managing databases face a trade-off between data storage efficiency and query performance, with normalized databases conserving memory but reducing query speed, while denormalized databases improve query speed but increase storage redundancy and ETL downtime.

Innovation Solution

A computing system automatically restructures databases by normalizing or denormalizing tables based on query patterns to balance read and write constraints, using a database-refinement process that generates candidate models and selects the optimal structure to enhance both query performance and data storage efficiency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Loss of substance

If the database is normalized to improve data storage efficiency, then redundant data is reduced, but query performance deteriorates

Engineering Contradiction:

Improveredundant dataVSAvoidquery performance

Core Design Contradiction:

Loss of substanceVSSpeed

Solution Approach 1:

The system dynamically adjusts database normalization levels based on workload characteristics. It automatically generates candidate models with varying degrees of normalization and selects the optimal model that balances storage efficiency and query performance for the current workload conditions

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system changes structural parameters of the database model by generating candidate models with different normalization levels. It evaluates multiple structural configurations and transitions between them based on performance requirements and storage efficiency goals

Inventive Principle:

Principle #35Parameter changes

2Speed

If the database is denormalized to improve query performance, then query speed is improved, but data storage efficiency deteriorates

Engineering Contradiction:

Improvequery speedVSAvoiddata storage efficiency

Core Design Contradiction:

SpeedVSLoss of substance

Solution Approach 1:

The system dynamically adjusts database normalization levels based on workload characteristics. It automatically generates candidate models with varying degrees of normalization and selects the optimal model that balances storage efficiency and query performance for the current workload conditions

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system changes structural parameters of the database model by generating candidate models with different normalization levels. It evaluates multiple structural configurations and transitions between them based on performance requirements and storage efficiency goals

Inventive Principle:

Principle #35Parameter changes

3Speed

If the database is denormalized to improve query performance, then searchability is improved, but ETL downtime increases

Engineering Contradiction:

ImprovesearchabilityVSAvoidETL downtime

Core Design Contradiction:

SpeedVSLoss of time

Solution Approach 1:

The system performs preliminary analysis of query patterns and workload characteristics before making structural changes. It evaluates candidate models and selects the optimal configuration in advance, minimizing disruption to ongoing operations and reducing ETL downtime

Inventive Principle:

Principle #10Preliminary action

Solution Approach 2:

The system dynamically adjusts database normalization levels based on workload characteristics. It automatically generates candidate models with varying degrees of normalization and selects the optimal model that balances storage efficiency and query performance for the current workload conditions

Inventive Principle:

Principle #15Dynamics

Data Source

PatentUS11886404B2Automated database modeling

Publication Date: 2024.01.30 OPTUM SERVICES IRELAND LTD

AI summary

In general, this disclosure describes techniques for automatically restructuring a database to improve one or more parameters of the database. In some examples, a computing system is configured to extract a set of columns and merge the set of columns into a new table of a first new candidate model of the database; determine a table of the database based on a number of columns of that are involved in query “where” or “join” clauses; merge the table with one or more connected tables in a second new candidate model of the database, wherein the one or more connected tables are connected to the table by at least one of the “where” or “join” clauses; select a model of the database from among the candidate models based on one or more parameters; and use the selected model as the current model of the database.