General question on DB normalization

[Dewald]

Hi all, I have what might well be considered a stupid question but fortunately none of you are my employer or former varsity prof so I'll ask away :) I've always endeavored to keep my databases normalized to the best of my ability and, for the most part, I think I'm fairly well set in that line of thinking. At my current company though some of our databases are anything but normalized. When I challenged my boss about it he responded that it was done deliberately because it is not supposed to be a relational database. It is a DB that accepts, stores and processes transactions at quite a fierce tempo and they didn't want every INSERT or SELECT statement to have to internally cross reference lots of other tables. Fair enough, it makes sense. But then it occurred to me that I probably don't know enough about DB design because I would never have thought of that. I would simply have designed a normalized DB because that's the way I'm programmed. So, I turned to Wikipedia but even there I can't seem to find an awful lot of information on non-normalized DB's but that's most likely because I don't really know what search terms to use. So my question is: 1. When should a DB be normalized and when not? 2. Can anyone point me to an online article or two that deals with the topic? 3. Am I correct in assuming that a normalized DB is called a "Relational Database"? 4. What would you call a DB like ours here that is not normalized?

[Mycroft Holmes]

Your boss may actually be right (I work with batch processes not transactions) because with every FK and index on a table the CRUD operation need to do the additional work to maintain the relationships. I still doubt it is a valid answer though, only a reporting database should not be normalised IMHO. Can't help more than that and I hope you find the ammo to shoot this one down.

Never underestimate the power of human stupidity RAH

[Dewald]

Thanks, but I'm not disputing that my boss is right. I think he has a valid point so I'm not really looking for ammo to shoot his argument down :) I'd just like to make sure I really understand DB design and not blindly stick to normalized DBs if that's not always the best way to go.

[Mycroft Holmes]

Ah but I think blindly sticking to normalised databases is the correct thing to do. The only exception I have found is if you are creating a summarised table for reporting, I often include the report descriptors in the summarised table. This has gone out of fashion as we are starting to use cubes to support our reporting and they are whole different matter.

Never underestimate the power of human stupidity RAH

[i j russell]

The term that you are looking for is Denormalization. In general, databases should always be normalized to at least 3NF unless there are specific areas where performance of reading/writing is a problem. However, most modern RDBMS like Sql Server are extremely efficient at joining tables using indexes so many of the reasons for denormalization are no longer applicable.

[Lost User]

Dewald wrote:

1. When should a DB be normalized and when not?

You normalize it by default, removing redundancy. Once the database becomes too slow (which is usually not due to the normalization, but lack of indexes and such) you start to optimize/de-normalize/add redundancy. You'll waste a bit more space and gain some speed.

Dewald wrote:

2. Can anyone point me to an online article or two that deals with the topic?

The Oracle FAQ[^] also contains a link to the reverse process.

Dewald wrote:

3. Am I correct in assuming that a normalized DB is called a "Relational Database"?

The fact that there are relations between the data doesn't mean that these have been normalized.

Dewald wrote:

4. What would you call a DB like ours here that is not normalized?

That depends on what you're using it for. If it's a blob-store or a container for hashtables, then I'd say that it's an optimized datastore and I'd make sure to give credit to the optimizer. If it's relational data that you're storing, then I'd call it an "accident waiting to happen" :)

I are Troll :suss:

[Dewald]

Great, thanks!

[David Mujica]

Actually Wikipedia has a good explaination of Normalization. http://en.wikipedia.org/wiki/Database_normalization[^] Chris Date and Edgard Codd have written numerous books on the topic and are frequently used in Universities as the course textbook. I had to study them when I was in Graduate School.