Denormalized data deliberately introduces controlled redundancy or less-normalized relationships to make frequent queries faster. It is a rare, last-resort optimization used when joins across several tables consume significant time, resources, or money. section 7.12 section 7.14
Use a careful two-step decision process:
- Identify frequent queries that join more than two tables and verify that they cause meaningful performance or cost problems. section 7.12
- Duplicate selected columns or restore a direct relationship so the query joins fewer tables. section 7.12
- Compare the performance benefit with the risks of inconsistent inserts, updates, and deletes. section 7.13 section 7.14
Music app: removing an unnecessary join
A normalized music database connects artists to songs through albums. Finding an artist’s top songs therefore joins artist, album, and song, even though album contributes no output data. Adding artist_id to song creates a direct artist–song relationship, allowing the query to join only artist and song. The change improves responsiveness but duplicates relationship information that must remain synchronized. section 7.12
Online store: restoring a direct relationship
Receipt queries may join user, payment_method, and purchase even though payment_method contributes little output. Restoring a direct user–purchase relationship simplifies the query, but introduces redundancy and violates BCNF. A stable email policy and account-only purchases reduce the risk of insertion and deletion anomalies, yet the change still requires a cost-and-benefit review. section 7.13
-- comment: works for MySQL, MariaDB, -- comment: and PostgreSQL -- comment: consult ChatGPT on adaptations -- comment: for other RDBMS SELECT artist.artist_name, song.song_title FROM artist INNER JOIN album ON artist.artist_id = album.artist_id INNER JOIN song ON album.album_id = song.album_id WHERE artist.artist_name = 'Nebula Nova' ORDER BY album.album_id, song.song_id LIMIT 5;
Compare the original three-table query with the shorter denormalized form in the related listing. section 7.12
