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.
Technical debt is a metaphor that describes the long-term effects of shortcuts taken in software development activities to achieve near-term goals. In this study, we explore a new context of technical debt that relates to database normalization design decisions. We posit that ill-normalized databases can have longterm ramifications on data quality and maintainability costs over time, just like debts accumulate interest. Conversely, conventional database approaches would suggest normalizing weakly normalized tables; this can be a costly process in terms of effort and expertise it requires for large software systems. As studies have shown that the fourth normal form is often regarded as the "ideal" form in database design, we claim that database normalization debts are likely to be incurred for tables below this form. We refer to normalization debt item as any table in the database below the fourth normal form.We propose a framework for identifying normalization debt. 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 identify debt tables. To manage such debts, we propose a trade-off analysis method to prioritize tables that are candidate for normalization. The trade-off is between the rework cost and the debt effect on the data quality and maintainability as the metaphoric interest. To evaluate our method, we use a case study from Microsoft, AdventureWorks. The results show that our method can reduce the cost and effort of normalization, while improving the database design. CCS CONCEPTS• Software and its engineering → Designing software;• Information systems → Relational Database Model;
scite is a Brooklyn-based organization that helps researchers better discover and understand research articles through Smart Citations–citations that display the context of the citation and describe whether the article provides supporting or contrasting evidence. scite is used by students and researchers from around the world and is funded in part by the National Science Foundation and the National Institute on Drug Abuse of the National Institutes of Health.
hi@scite.ai
10624 S. Eastern Ave., Ste. A-614
Henderson, NV 89052, USA
Copyright © 2024 scite LLC. All rights reserved.
Made with 💙 for researchers
Part of the Research Solutions Family.