Chapter 2: Tuning the Relational Database Model

---

So how do we go about tuning a relational database model?

2.1 Normalization and Tuning

What does Normalization have to do with tuning? There are a few simple guidelines to follow and things to watch out for:

• Too little Normalization leads to too much duplication of data and thus your database will get too big. Too much data will lead to slow access times due to having too much disk space to search through.

• Incorrect Normalization should be obvious: convoluted and complex application code with nowhere to go but a rewrite or the garbage pile, the final resting place of many failed commercial applications.

• Too much Normalization leads to overcomplex SQL code which can be difficult, if not impossible, to tune. I tend to pass by 4th and 5th Normal Forms and think very carefully about the usefulness of 3rd Normal Form derived entities.

• Quite often databases are designed without knowledge of applications. The data model could be built on a purely theoretical basis. Later in the development cycle, the application not only has difficulty using a highly granular data model but the data may be structured totally differently to that of the application. One possible answer is that both development and administration people should be involved in data modeling. Another possibility is that the data model supports the application and thus should be built with all knowledge in hand. This is of course impossible. Thus, it should be acceptable to alter the data model at least during the development process, possibly substantially. Most of the problems with relational database model tuning are Normalization related. Normalization should be simple because it is simple!

  Tip 

  This is why I attempt to simplify Normalization. Many data models are designed and built from a theoretical perspective. Theory often does not work perfectly commercially. Normalization is based on simple set theory. Normalization in its most basic form is akin to those little circles and intersections and things you used to draw and color at school (in America that would probably be in junior high).

• Watch out for excessive use of outer joins in SQL code. If you have a lot of outer joins it could mean that your data model is too granular; you could have overused 4th and 5th Normal Forms. 4th and 5th Normal Forms are rarely needed in the name of efficiency. An outer join is akin to: "Go and get this. Oh! Go and get that too because this doesn't quite cover it."

The other side of Normalization is of course Denormalization, which we will get to later. Let's start with taking a look at Referential Integrity.

---