| There are two main reasons to denormalize: security, and performance. The former (security) is something I have only ever had to deal with twice. If your going to cordon off data based on some arbitrary ruleset (3rd party) it is a path you can take. For transactional integrity, and from an operations perspective both cases were perfectly valid. The latter is one I have also only seen rarely. I have seen it done to keep a history of actions without cluttering up the primary table. As an example I worked on a DB with millions of entries for users, and a separate table for emails, when the user updated their email, it would add a new row, verify it and then fetch the newest complete row when the user was queried. Getting rid of all the history in that table provided a fairly significant performance bump. The second was to split little used columns in a single table out from the frequently accessed ones. The resulting trimmed table then fit in memory and was orders of magnitude faster than going to disk. Is there ever a case to denormalize and duplicate? There are a few: OLAP systems are unique beasts, and duplicate and denormalize in many a strange way, and all in the name of performance and ease of use for end users. The reality is that these systems usually have a SINGLE point of entry for adding and updating records (that aren't materialize views of end users). OLAP systems are weird, because nothing makes sense in them, but most of them (with all their duplicate data) aren't primary stores. Oddly I have replaced a handful of OLAP RDMBS with document stores... The other major reason to duplicate is for "snapshotting". If you need to know the state of one record when another record was created then your probably going to end up with duplicate information. IN every case where I have seen this the words "compliance" and "audit trail" are part of the conversation. However rather than being problematic the decoupled nature of the duplicate record is desirable. |