Your goal should be to get to the 3rd normal form - and possibly the Boyce-Codd Normal Form. Jim Booth and I had an in-depth discussion on this very topic on this forum,

The deal is, you have normalized your tables or you have not. If you do something more, that by definition is not normalization. That is something else. Are you with me here?

Whether you normalize to 4th or possibly 5th normal form is another thing entirely. The farthest I have gone is the 4th. form.

There are lots of good reasons to de-normalize. Query performance is partially a good reason. Before munging the requirements of a transaction-oriented database and a database optimized for reporting purposes, I would investigate the use of a data warehouse. Ultimately, you do what you need to do...

Another reason for breaking normalization is maintain integrity in your reporting. For example, let's say you have item descriptions that can change. In a lineitems table, you might elect to carry the description that existed when the lineitem was created. This way, when your item descriptions change, your past reports won't change.


One other thing to keep in mind. Before you can really get down to answer the question of whether normalization is your problem, you need to make sure the design is complete. I can't answer that for you. The point is that normalization may not be your problem. Rather, you may be mis-applying concepts - and what you think is normaization - isn't.

< JVP >



>Can data be 'over' normalized? As I'm sitting here trying to decipher the 20 to 30 nearly perfectly normalized related tables in this program, I'm seriously starting to question how much is too much. The data manipulation at our disposal with well normalized tables is phenomenal, but at what cost? Is there a limit to the complexity of relations the mind can accurately maintain? Are there any rules of thumb as to what that is? This then begs the question of how best to denormalize certain data.
>
>I was just looking at the VFP SCX structure for kicks (yep, that's what they call fun out here in the boonies). It seems awfully de-normalized to me. I would think each class library should consist of several tables, not just one. Shouldn't they consist of a class table, and perhaps a properties table, a methods table, etc.? Why does the company that develops this database system not take advantage of this normalization capability?