Prioritizing Technical Debt in Database Normalization Using Portfolio Theory and Data Quality Metrics

by   Mashel Albarak, et al.

Database normalization is the one of main principles for designing relational databases. The benefits of normalization can be observed through improving data quality and performance, among the other qualities. We explore a new context of technical debt manifestation, which is linked to ill-normalized databases. This debt can have long-term impact causing systematic degradation of database qualities. Such degradation can be liken to accumulated interest on a debt. We claim that debts are likely to materialize for tables below the fourth normal form. Practically, achieving fourth normal form for all the tables in the database is a costly and idealistic exercise. Therefore, we propose a pragmatic approach to prioritize tables that should be normalized to the fourth normal form based on the metaphoric debt and interest of the ill-normalized tables, observed on data quality and performance. For data quality, tables are prioritized using the risk of data inconsistency metric. Unlike data quality, a suitable metric to estimate the impact of weakly or un-normalized tables on performance is not available. We estimate performance degradation and its costs using Input (IØ) cost of the operations performed on the tables and we propose a model to estimate this cost for each table. We make use of Modern Portfolio Theory to prioritize tables that should be normalized based on the estimated IØ cost and the likely risk of cost accumulation in the future. To evaluate our methods, we use a case study from Microsoft, AdventureWorks. The results show that our methods can be effective in reducing normalization debt and improving the quality of the database.


page 1

page 2

page 3

page 4


Database Normalization Debt: A Debt-Aware Approach to Reason about Normalization Decisions in Database Design

Technical debt is a metaphor that describes the long term effects of sho...

Identifying and Managing Technical Debt in Database Normalization Using Machine Learning and Trade-off Analysis

Technical debt is a metaphor that describes the long term effects of sho...

Is 2NF a Stable Normal Form?

Traditionally, it was accepted that a relational database can be normali...

Row Conditional-TGAN for generating synthetic relational databases

Besides reproducing tabular data properties of standalone tables, synthe...

Auto-Tables: Synthesizing Multi-Step Transformations to Relationalize Tables without Using Examples

Relational tables, where each row corresponds to an entity and each colu...

Exploring Query Results

Users typically interact with a database by asking queries and examining...

Dynamic Move Tables and Long Branches with Backtracking in Computer Chess

The idea of dynamic move chains has been described in a preceding paper ...

Please sign up or login with your details

Forgot password? Click here to reset