On the effects of logical database design on database size, query complexity, query performance, and energy consumption

TL;DR

This paper tackles the practical question of how database normalization affects on-disk size, query complexity, throughput, and energy consumption. It adopts an empirical, read-only benchmark on the IMDb dataset using PostgreSQL, comparing NF forms 1NF, 2NF, and 4NF. Key findings show that 1NF→2NF reduces storage by about 10.3%, boosts throughput by approximately $311\%$, and lowers energy per transaction by about $74\%$, while the gains from 2NF→4NF are modest and accompanied by a modest storage increase. The results highlight substantial efficiency benefits of moving from 1NF to 2NF in read-heavy contexts, while advising caution in pursuing higher NF forms without considering the trade-offs in complexity and generalizability.

Abstract

Database normalization theory is the basis for logical design of relational databases. Normalization reduces data redundancy and consequently eliminates potential data anomalies, while increasing the computational cost of read operations. Despite decades worth of applications of normalization theory, it still remains largely unclear to what extent normalization affects database size and efficiency. In this study, we study the effects of database normalization using the Internet Movie Database (IMDb) public dataset and PostgreSQL. The results indicate, rather intuitively, that (i) database size on disk is reduced through normalization from 1NF to 2NF by 10%, but not from 2NF to 4NF, (ii) the number of tables and table rows in total increase monotonically from 1NF to 2NF to 4NF, and that (iii) query complexity increases with further normalization. Surprisingly, however, the results also indicate that (iv) normalization from 1NF to 2NF increases throughput by a factor of 4, and consequently, (v) energy consumption per transaction reduces by 74% with normalization from 1NF to 2NF. The results imply that the gains of normalization from 2NF to 4NF in terms of throughput and energy consumption are minimal, yet increase the storage space requirements by approximately 7%. While these results represent merely one specific case, they provide needed empirical evaluation on the practical effects and magnitude of database normalization.

Figures (5)

• Figure 1: IMDb database schemas normalized into 1NF, 2NF and 4NF; square brackets after column names in the baseline schema represent compound values; some foreign keys -- although not enforced by implemented constraints -- are presented in blue to clarify overlapping foreign keys
• Figure 2: Database sizes by several metrics; baseline is the original NFNF IMDb database
• Figure 3: Query trees for queries 1..4; nodes represent tables, bowties joins, $\sigma$s selection and $\pi$s projection
• Figure 4: Query trees for queries 5..7; nodes represent tables, bowties joins, $\sigma$s selection and $\pi$s projection
• Figure 5: Throughput averages show the 2NF and 4NF databases outperforming the 1NF database; transaction processing in the 1NF database is subject to more fluctuation than transactions processed in the 2NF and 4NF databases; on average, the transactions processed in the 2NF and 4NF databases consume less energy than transactions processed in the 1NF database; the creation of the 2NF database consumes less power and time when compared to the 4NF database, which in turn consumes less power and time than the creation of the 1NF database;