Prioritizing technical debt in database normalization using portfolio theory and data quality metrics

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.

FIXME: Synchronize with Database An Empirical Study of Data Access Self-Admitted Technical Debt

“…Several researchers have investigated self-admitted technical debt in source code for various purposes including its identification [13,14,44,16,26,53,54,1], removal [57,28], prioritization [3,2,20], recommendation when to admit SATDs [55], or the analysis of its impact on source code quality [51] -to mention a few examples.…”

“…Sierra et al [43] present a survey of SATD studies from 2014 to 2019. They identified three main categories of research contributions: (1) papers that focus on the detection of SATD, (2) papers that aim to improve the comprehension of SATD, and (3) papers that focus on the repayment of SATD.…”

Technical Debt Prioritization: State of the Art. A Systematic Literature Review

“…Their approach returns the TD items that consume the largest maintenance eort and therefore deserve more attention and higher priority for refactoring Plösh et al proposes a TD prioritization approach with a primarily focus on the prioritization of Design debt, and their approach relies on the quantification of design best practices by transferring the identified TD items into a portfolio-matrix [SP42]. Albarak and Bahsoon further claim that software systems having database tables below fourth normal form are likely to form TD and therefore the ill-normalized tables should be prioritized for refactoring [SP40].…”

Towards a Catalog of Enterprise Architecture Smells

“…While the Technical Debt metaphor has further extended to include e.g. database design debt, which describes the immature database design decisions [6], the context of Technical Debt is still limited to the technological aspects.…”