A few questions about Normalization

[Steven Cappiello]

I'm learning about data modeling right now and was wondering,

Could any of you share with me... what is your real-world approach to normalization and de-normalization ?

When do you know you need to normalize and when is it not worth the effort ?

And, can anyone give me an example of when they might need to denormalize :

thanks in advance for any real-world insight you can offer.

[Fenton]

This is a big question. And, while I have a fair idea what the rules of normalization are, I only think of such things as they apply to and are implemented in FileMaker. Versions 7 & 8 have removed some of the problems which forced us to "denormalize" more earlier.

In 7 the speed of relational access is many times the speed in 6. So there is seldom a need for redundant data for such things as searching; unless speed is really a problem (due to either size of the table, or user use). Tunneling data eliminates the need for redundant data in order to view it in portals, etc.. Compound relationships reduce the need for using concatenated calculation fields for keys.

There are still instances however when you need data somewhere where it would not absolutely need to be otherwise. Often this has to do with relational filters. A recent post about filtering "sequences" is a good example. The "top" primary ID of the solution was a Patient ID. The sequence data was a couple TOs further down the line. But it was to be summarized in the top Patient table, filtered via "regions" (which were unstored text calculations used as keys).

So the Patient ID had to be passed into the sequence table, as an indexable field. It was redundant data. But in this case it was needed. It would also be needed if reports were done in that "end" data table. Another important factor is that it was never going to change after data entry.

Another time that redundant data is needed is if you need to filter a relationship, for say a value list, but all the fields are not in the current table (sort of the opposite of the previous example). You can't use fields for a filter very well if they are not in the current table. But you can create an unstored calculation, to get the field's value from another related TO. I don't know if I'd really call this "redundant" data however, as it's unstored and is basically the same field from somewhere else; I don't know that other database applications do things like that.

Altogether I try to be as "normal" as I can, as long as it works.