• GilaMonster (10/28/2010)


    Normalise fully. 4th normal form is probably a safe place to stop. Denormalise as necessary and with good reasons. Not bothering to normalise past 1st is not denormalisation, it's lazyness.

    It's not just laziness. It's also stupidity and irresponsible negligence.

    But I don't like to start at 4NF, instead I usually start out aiming at EKNF, using Bernstein's algorithm to eliminate functional dependencies. That results in a schema that is in EKNF (hence also in 3NF) and has the respresentation property - every functional dependency is represented by the keys. At that point I look to see whether I want any more normalisation - I don't want multivalued dependencies because I don't want to have to code around update and insertion anomalies, but I also don't want to sacrifice the representation property and have to produce code to suppert functional dependencies. In cases where there are no multivalued dependencies I'm already in 4NF so I'm happy; if there are multivalued dependencies that can be eliminated without sacrificing representation, I eliminate them (and if all my multivalued dependencies get eliminated, I'm in 4NF, so happy again). If there are still some multifunctional dependencies I have to decide for each one whether to normalise it out and accept the difficulty of handling some unrepresented functional dependencies, or leave it in and accept the penalty of dealing with it in code - but this is a fairly rare situation, most business problems can be handled by a schema that both is 4NF and has the representation property (it's a pity that they can't all be done that way).

    In effect, rather than starting from 4NF and denormalising where neccessary, I start from EKNF and do additional normalisation where it's both useful and sensible. I suspect the results of both approaches will be the the same, but the means of getting there is different.

    Tom