Database normalization is one of the main principles for designing relational databases, which is the most popular database model, with the objective of improving data and system qualities, such as performance. Refactoring the database for normalization can be costly, if the benefits of the exercise are not justified. Developers often ignore the normalization process due to the time and expertise it requires, introducing technical debt into the system. Technical debt is a metaphor that describes trade-offs between short-term goals and applying optimal design and development practices. We consider database normalization debts are likely to be incurred for tables below the fourth normal form. To manage the debt, we propose a multi-attribute analysis framework that makes a novel use of the Portfolio Theory and the TOPSIS method (Technique for Order of Preference by Similarity to Ideal Solution) to rank the candidate tables for normalization to the fourth normal form. The ranking is based on the tables estimated impact on data quality, performance, maintainability, and cost. The techniques are evaluated using an industrial case study of a database-backed web application for human resource management. The results show that the debt-aware approach can provide an informed justification for the inclusion of critical tables to be normalized, while reducing the effort and cost of normalization.

In previous work, we explored a new context of technical debt that relates to database normalization design decisions. We claimed that database normalization debts are likely to be incurred for tables below the fourth normal form. We proposed a method to prioritize the tables that should be normalized based on their impact on data quality and performance. In this study, we propose a framework to identify normalization debt items (i.e. tables below the fourth normal form) by mining the data stored in each table. Our framework makes use of association rule mining to discover functional dependencies between attributes in a table, which will help determine the current normal form of that table and reveal debt tables. To illustrate our method, we use a case study from Microsoft, AdventureWorks database. The results revealed the applicability of our framework to identify debt tables.

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.