A question regarding the performance of normalization

Question

I am sorry this is such a newb question, but it is one thing I am having a difficult time wrapping my mind around, and after an evening spent reading I finally just decided to ask for some clarification.

Say I have a table that contains information about servers. As new servers come online, this table is populated with information about the servers. Now, assume that the server name is unique, and server serial number is unique.

Scenario #1:

Server Table
server_name text,
server_serial text,
server_os integer (FK points to os table),
os_version integer (FK points to os_version table),
cpu_type integer (FK points to cpu_type table),

Now, let's assume there are 20 things that will not be unique about this server (e.g., server_os is not unique), thus in Scenario #1 we have 20 columns that contain FKs pointing to PKs of other tables.

Scenario #2:

No normalization. No other tables, just a server table with all the columns related to the server. No FK/PK relationships.

Normally I would go for Scenario #1. However, in scenario #1 I end up with a lot more queries. For scenario #2, I would simply do an insert for each server. In scenario #1, I would need to do a select to grab the PKs for all 20 columns, and then do my insert with the returned values (21 queries assuming all values exist in the 20 other columns). It gets even worse if there's a new server_os, cpu_type, etc.

Any help understanding why scenario #1 (normalized) is better in this situation would be greatly appreciated, or what exactly I am missing here. Thank you!

Answer 1

The relationship between normalization and performance is complicated. Sometimes the pursuit of normalization and the pursuit of high performance lead in the same direction, sometimes in opposite directions. You have to look at each case carefully.

More importantly, there is more than one measure of "goodness" in a database. In addition to performance, there is ease of programming, ease of understanding, adaptability in the face of changing requirements, and so on.

Learning how to normalize is just the beginning. In addition to learning the basic normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), you should learn what benefit you get out of conformance to each of these normal forms. In general, conformance to 1NF buys you keyed access to all data. conformance to 2NF through 5NF buys you freedom from harmful redundancy. Once you know the benefits, you can compare them against the costs.

A database that contains harmful redundancy has the possibility that the database will contradict itself, by storing contradictory predicates in two different places. You can exercise discipline in avoiding self contradiction by being very careful when you write the application. But it's often better to design the database in such a way that it can't contradict itself, and normalization helps here.

Normalization sometimes helps a database to avoid "hot spots" at update time, where the DBMS is forced to serialize update transactions, and thereby creates bottlenecks. But extracts and reports are often slower and more cumbersome than they are on a well designed database that is not normalized.

If you do decide not to normalize, don't just design at random, composing tables in any way that seems good at first sight. Adopt some design discipline other than normalization to guide you to a good design. Star schema is one such alternative design discipline.