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

TL;DR

This paper introduces a pragmatic approach to prioritize database normalization efforts by applying Portfolio Theory and data quality metrics to mitigate technical debt and enhance database performance and integrity.

Contribution

It proposes a novel method combining Portfolio Theory with data quality and performance metrics to prioritize normalization of database tables, focusing on cost-effective debt reduction.

Findings

Effective reduction of normalization debt demonstrated in case study

Improved data quality and performance metrics after applying the method

Prioritization based on I/O cost and data inconsistency risk enhances normalization strategy

Abstract

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\Output (I\O) 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\O 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.