In terms of databases, is “Normalize for correctne

Question

Normalization leads to many essential and desirable characteristics, including aesthetic pleasure. Besides it is also theoretically "correct". In this context, denormalization is applied as a compromise, a correction to achieve performance. Is there any reason other than performance that a database could be denormalized?

Answer 1

Normalization has nothing to do with performance. I can't really put it better than Erwin Smout did in this thread: What is the resource impact from normalizing a database?

Most SQL DBMSs have limited support for changing the physical representation of data without also compromising the logical model, so unfortunately that's one reason why you may find it necessary to demormalize. Another is that many DBMSs don't have good support for multi-table integrity constraints, so as a workaround to implement those constraints you may be forced to put extraneous attributes into some tables.

Answer 2

The two most common reasons to denormalize are:

1. Performance
2. Ignorance

The former should be verified with profiling, while the latter should be corrected with a rolled-up newspaper ;-)

I would say a better mantra would be "normalize for correctness, denormalize for speed - and only when necessary"

Answer 3

You don't normalize for 'correctness' per se. Here is the thing:

Denormalized table has the benefit of increasing performance but requires redundancy and more developer brain power.

Normalized tables has the benefit of reducing redundancy and increasing ease of development but requires performance.

It's almost like a classic balanced equation. So depending on your needs (such as how many that are hammering your database server) you should stick with normalized tables unless it is really needed. It is however easier and less costly for development to go from normalized to denormalized than vice versa.

Answer 4

Denormalized data is much more often found at places where not enough normalization was done.

My mantra is 'normalize for correctness, eliminate for performance'. RDBMs are very flexible tools, but optimized for the OLTP situation. Replacing the RDBMS by something simpler (e.g. objects in memory with a transaction log) can help a lot.

Answer 5

Reporting system and transaction system have different requirements.

I would recommend for transaction system, always use normalization for data correctness.

For reporting system, use normalization unless denormaliztion is required for whatever reason, such as ease of adhoc query, performance, etc.

Answer 6

Denormalization normally means some improvement in retrieval efficiency (otherwise, why do it at all), but at a huge cost in complexity of validating the data during modify (insert, update, sometimes even delete) operations. Most often, the extra complexity is ignored (because it is too damned hard to describe), leading to bogus data in the database, which is often not detected until later - such as when someone is trying to work out why the company went bankrupt and it turns out that the data was self-inconsistent because it was denormalized.

I think the mantra should go "normalize for correctness, denormalize only when senior management offers to give your job to someone else", at which point you should accept the opportunity to go to pastures new since the current job may not survive as long as you'd like.

Or "denormalize only when management sends you an email that exonerates you for the mess that will be created".

Of course, this assumes that you are confident of your abilities and value to the company.