Re: Help! I can't support normalization

> I would like a well supported argument to
> put this silliness to rest. I appreciate anyone's thoughts and ideas.
> Please enlighten me!

I'm going to play devil's advocate on this one. It is NOT true that you "must" normalize in every situation, for every database. I have built databases that are not fully normalized, and that meet the needs quite well, thank you. However, just because they weren't fully normalized doesn't mean that they were designed without discipline. It's just that there's more than one discipline you can follow.

Second, even in cases where it's advisable to normalize, there is no single pat answer to the question "why?" The single most general answer, "because my database guru told me to" will not persuade anybody.

Having said that, there are some pretty ubiquitous problems that crop up over and over again, whenever denormalized data has to be updated. With a little practice, you'll be able to pull them up on the fly, when a colleague quizzes you about it.

In general, departing from full normalization results in one fact being stored in many places. Databases that suffer from this generally suffer from three kinds of anomalies: update anomalies, insert anomalies, and delete anomalies.

update anomalies are the simplest, if a single fact, like professor Smith's phone number, is stored in multiple places, like under each course that professor Smith teaches, then it will take longer to update this fact when the phone number changes. Worse yet, if a program with an error in it does the update, it's possible that the old phone number will still appear in some of the places, while the new phone number will appear in others. That's inconsistent.

Worse yet, there will be insert anomalies. You can't store the phone number for any professor that isn't reaching any courses. And if you "create a dummy course" just so you have a slot under which to store the phone number, you're asking for trouble.

There will also be delete anomalies. Whenever a course is over, and you go to delete the row about the course from the course table, you MIGHT just be erasing the last record of the professor's phone number. You don't want that, do you?

And so it goes. In your own case, you might have to think a little, at first, to come up with the anomalies. In some cases, there are counter arguments, and sometimes those arguments will prevail, because the counter arguments are more relevant to the application.

And sometimes even the best argument doesn't prevail, simply because the other person is the kind that "never loses an argument". Received on Fri Nov 01 2002 - 20:17:57 CET