David,

Your question is a very good one. My answer is this, a database CAN be over designed, that is designed to be more complex than is necessary. However, I would point out that you used the term Normalized in your message and then went on to discuss things that are not normalization. Normalization is a fixed set of 6 rules that evaluate the level of redundancy and the effectiveness of the Primary key dependencies within a specific table's design. Often the repair for a problem with the rules of Normalization is move certain data to other tables.

There are many other considerations, besides normalization, that are important in designing a relational database. One key issue to keep in mind, when applying the rules of normalization, is that the Normalized design is optimized for data entry and referential integrity enforcement and is often deoptimized for data retrieval. The rules of normalization are designed to simplify the process of preventing data inconsistencies. Data stored in one place, by definition, cannot be inconsistent, so normalization is aimed at removing duplicate storage of data elements. Many times it is more efficient in retreiving data to have a value in the a certain place even though it is also stored in another place, this is contrary to normailization but it is NOT contrary to sound relational design.

The issue is this, one needs to know what the rules are, what following the rules does, what breaking the rules costs, and what steps one needs to take to protect the data integrity when the rules have been broken. Once one knows these things then it is prefectly acceptable to break the rules, as the person knows exactly what the potential problems are and exactly what steps are required to protect against those problems.

I find myself asking this question early in my data design work, what is the primary purpose of this data system, is it the entry and recording of events, or is it the production of information (reports) for decision support actions? The answer to this question answers the bigger question as to how far should I go with normalization.

I also, always, normalized to the 5th normal form (5th NF is the 6th rule as one of the rules has a name instead of a number). After that I decide how far to back up from that level of normalization by applying the process of denormalization as described by Chris Date. I have found that normalizing finds many unseen problems in my designs and fixes them. I do not want to stop finding those problems so I fully normalized and then back off when appropriate rather than short circuiting the normalization process.