How does one know when to stop normalizing?

Question

Alright, so basically I feel that I tend to over-normalize things, and maybe I am doing it at the cost of performance. So, to illuminate on the problem, I have created the following schema to use as an example:

As you can see, I have outlined two different approaches. The idea here is that all universities have programs (e.g., Engineering), and all programs have majors (e.g., Electrical Engineering). For this example to work, we must assume that there are 40 programs, and say 1,000 majors, and that schools have the same programs/majors.

Now, my typical approach in this scenario is to take anything that may be repeated (i.e., majors and programs), and put those items into their own table; then have a relationship, as modeled above. Another approach, one I tend to stay away from, is the second model, in which program and major are columns with repeated values (e.g., Engineering may be repeated 1,00s of times over the table). Basically, if the value is repeated, I create a table for it.

Now, I'm not so much interested in which one of these is the better approach, since I am only using them as an example to illuminate the true question: How does one know when they are over-normalizing? I know that there is a point when you're going too far in normalizing your tables, but I never quite know what the measurement is.

Addendum

A university need not have all the majors in a program, thus the reason universities are tied to majors, not programs (e.g., University X has an Engineering school but does not have Nuclear Engineering, which is part of the Engineering program).

Answer 1

When designing a database, normalization is a process undertaken when producing the logical model i.e. the relational data model. Performance is an attribute of the physical model; the implementation of the logical model on a particular DataBase Management System.

You cannot "over-normalize" a relational data model. Either the relational data model is in fifth normal form or it is in a lower normal form and update anomalies exist.

The normalization process is a well-defined procedure for analyzing the functional, multivalued and join dependencies of the relations within the model and taking projections of these relations to eliminate any dependencies not implied by the candidate key.

Assuming you have a relational data model in fifth normal form which has been implemented on a particular DBMS. At some point the performance of this implementation has become unacceptable. How you decide to address this issue will depend on the DBMS that is being used and the features that DBMS makes available.

Having looked at the features available to you for the DBMS being used you may decide to take some of tables in the DBMS and re-factor them so that they are in a lower normal form. (Note: the logical model has not changed, you have changed the physical model to address a performance issue with the implementation.) By re-factoring these tables you have introduced the possibility of update anomalies and, because your relational data model is in fifth normal form, you can determine precisely where these may occur. Having determined these you may choose to ignore them, or add some additional processing to either identify them and/or address them. (Note: by adding additional processing you may impact performance such that the perceived benefits of "denormalizing" these tables is actually negated.) In any case you are able to make an informed analysis of any action you may take.

Answer 2

I think the answer here is at least very close to, if not the same as, the answer to "how close to a particular ideal should we strive for to reach this goal?" Different steps along the way in the development life cycle, different disciplines (within software development: application programming, database design/programming, UI, QA, etc) have various ideals. Must all projects have n-tier approaches? Is it ever forgivable to put application logic in the database? Can it even be suggested to use a cursor (I said that quietly so that nobody would hear it ;-)? The question comes down to:

1. Is the current state of the task at hand leave the project with something that works and can be maintained? Or is it the bare minimum to make it appear as if things are working?

and:

2. What is the gain for the extra time / effort required to move closer to the ideal?

If the current state is either barely functional and/or barely maintainable, then the second question is easier to answer: the gain for moving closer to the ideal is a system that works (less wasted time in the future, constantly coming back to fix things) and is maintainable (less wasted tie in the future when you either have to fix something or requirements change and new functionality is requested).

The main difficulty in answering these questions, though, is that it requires experience. It requires having worked on several projects, having tried different approaches to problems, and seeing what worked in which situations and didn't in other or maybe wouldn't really ever be a workable idea.

With the two example models given, the one with the repeating fields (i.e. less normalized) is functional, but over time will show itself to be harder to maintain and harder to adapt to changes necessitated by new requirements. I know this from experience as I have had to work on system where this approach was taken. Not only did it cause problems and was harder to debug (people manually entering those string values over time makes for less consistent / predictable data than was ever imagined possible by anyone choosing to go down that road at design time), but it also slowed queries down quite a bit as filtering on text fields isn't as fast as filtering on integer fields.

But experience is also required to know if you have gone too far. Or maybe gone the appropriate distance but in a slightly inappropriate direction. Take the first model shown, for example. There are two modeling mistakes:

1. The "university_major" table should not have "program_id" in it. "program" should relate to "major" and can be inferred from "university_major" through its relationship to "major".

2. The "university_major" table should not have the "university_major_id" field. It is a redundant key that adds no value due to being a surrogate, auto-incrementing value. There is a natural key available to be the PK: "university_id", "major_id". The arbitrary nature of "university_major_id" would allow for multiple instances of "university_id" and "major_id" unless an additional Unique Index or Constraint is added for those two fields.

And again, it is mainly experience that lead me to those thoughts. I also realize that there are situations when a system needs a single auto-incrementing value (I believe replication in SQL Server requires it on all replicated tables), and so you go with an IDENTITY field for the PK and add the UNIQUE INDEX and move on with life ;-).

So I would say there is no actual, measurable way to know when to stop normalizing. It mainly comes down to experience. I would also add that collaboration with others (to make use of their experience) and assessing the current project (allotted time and resources, target audience, etc) play a role. But I would also argue that someone's willingness and/or ability to recognize / value / make use of those other factors is, at least partially, a proportional to their level of experience.