1

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:

enter image description here

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).

2 Answers 2

5

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.

1

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:

  1. 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.

7
  • Interesting to read through your reply. I'm really quite surprised that there isn't some way to measure normalization vs. performance, or any hard and fast rules regarding normalization. In this way I find database design to be quite a bit different than programming. In programming, for the most part, you have agreed upon best practices, algorithms, and so on. With database design things seem a bit more loose, for lack of a better term. I think point #1 (no program_id in major table) is no longer relevant with my edit, though I could be wrong. Oct 31, 2014 at 6:08
  • As for point #2, I notice that surrogate vs. natural keys is a hotly debated topic in database design. I've seen many people advocate that all tables should just have a surrogate key. However, sometimes that just doesn't seem to make sense to me. Say for example a table containing countries. I see no reason not to have the country name be a natural key. The arguments I've read against doing you've outlined in your reply: joins/filters on text fields are not as efficient as joins on integer fields. Oct 31, 2014 at 6:10
  • Finally, I think the main thing I've learned in all my research now on databases (here on this site, blogs, and reading Database Design For Mere Mortals) is that one should not get hung up on the design of their database. It can be refactored. Quite simply, design something; even if it's not 100% right, it's better than nothing at all. And so, the database I am designing is not perfect, but any issues that present themselves as a result of the database design can be fixed along the way. Oct 31, 2014 at 6:13
  • 1
    @DanielAmaya, I will update my answer later to clarify a few points, but: there are best practices but not everybody knows all of them nor when to apply them, and they differ between RDBMSs. There is a correlation between normalization and performance as that is one of the main intents of normalization, but as you said, database design/programming is a much different beast. Databases are primarily concerned with physical storage and secondarily with memory and CPU. App code is the opposite. The other difference is that database performance is highly dependent upon state (i.e. the data). Oct 31, 2014 at 15:29
  • Meaning, a good design for one scenario might perform poorly in another. This is not just data volume but also data patterns/distribution. For #1 and the Addendum, it is still wrong because a University isn't going to have a Program with 0 Majors in it, which is what putting it "university_major" allows for. In fact, the table is even called "university_major" which denotes the relationship right there :). The only reason to have "program_id" in "university_major" would be to handle the same "major" being in diff "programs" at diff "universities". Then "program_id" wouldn't be in "major". Oct 31, 2014 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.