Problem solve Get help with specific problems with your technologies, process and projects.

How does normalization affect good design for OLAP purposes?

How does Fabian Pascal's insistence on normalization affect good design for OLAP purposes?

On the issue of normalization, I agree with Mr. Pascal completely. Many people think I'm a maniac about normalization, but that is largely because I've been hurt by the lack of it too many times.

While others will argue with my opinions (from both sides), in a small database, normalization isn't a huge deal. Joe Celko will say that I don't push normalization far enough since I will usually settle for third normal form. The average programmer will probably say that I go way too far, making them do too many joins to get related data. I try to keep things "middle of the road", such that it isn't too hard to understand or explain the database design, but the design will still support millions of rows of data on "normal" hardware.

When you have only a dozen rows in one table, it doesn't really matter how normalized they are. As the database grows to thousands of rows in dozens of tables, normalization becomes much more important. When it grows to millions of rows in hundreds of tables, it will either be highly normalized or dangerously unstable!

As Mr. Pascal pointed out in his recent article, normalization is a logical concept, and performance is a physical one. You can't determine performance from an ERD diagram any more than you can derive a database schema from a TPC measurement. The two are casually related in that poor normalization will never generate good performance, and good performance implies at least relatively good design, but I've never found any hard one-to-one relationship between normalization and performance.

A database that is not normalized will allow bad (incorrect) data to be stored. This more or less insures that bad data will eventually creep into the database. These problems will haunt everyone that uses the database, and they will especially bedevil the data warehouse builder!

For More Information

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.