Normalization Exceptions

[kd2017]

Purely for sake of discussion I'm curious to hear some different perspectives on this:

• under what circumstances do you break normalization rules?
• To what degree do you normalize? Which minimal 'normal form' do you strive to achieve?

[Minty]

I think June (and others) have frequently used the expression
"Normalise until it hurts, then denormalise until it works!"

There is a lot of good reading directly related to this question in this thread
https://stackoverflow.com/questions/...ize-a-database

In my experience there are times when for the sake of performance or simplicity of design, you deliberately store something somewhere you shouldn't.
E.g. a grand parent ID in a "child of a child" table that is available through the first child because I have to duplicate all the sub-records and it simplifies the process.

[kd2017]

In my experience there are times when for the sake of performance or simplicity of design, you deliberately store something somewhere you shouldn't.
E.g. a grand parent ID in a "child of a child" table that is available through the first child because I have to duplicate all the sub-records and it simplifies the process.

This is exactly what I'm experimenting with at the moment and what prompted this post.

I'm testing whether or not it's reasonable to some how use grandparent id to simply enforce data integrity.

For example, [Table A] has children [Table B] and [Table C], and [Table B] and [Table C] together have child [Table D].
For each row in [Table D], the parents in [Table B] and [Table C] must have the same grandparent from [Table A]. How to *easily* enforce this without code or triggers? Include [Table A]'s id within [Table D] and link up relationships by using multiple fields...?
For illustrative purposes only:


We have redundant unique indexes in B and C, and a redundant grandparent key in D which is a normalization violation, but as far as I can tell it's a *hack* that enforces the rule given above... after all, enforcing *data integrity* is the prime directive isn't it?

[Amicron]

I will denormalize tables sometimes in order to speed up a really slow performing database. For example if you run a lot of reports that depend on the amount that a customer has spent with you over their lifetime you may want to store that value in the customer table which goes against normalization rules but will definitely speed up your reports. I made a video about it here.