Before reading what is below please realize I have never designed a DB for any one or organization with the exception being what I have dabbled with out of textbooks.

Over the last two weeks, I have researched DB normalization for days, literally.

"The Key, The Whole Key, and Nothing but the Key, so help me Codd."

OK. Finally, I found this suspected guru, Pinal Dave:

SQL SERVER – Effect of Normalization on Index and Performance Journey to SQL Authority with Pinal Dave.

and I agree with him, after you fully understand a client's needs normalize/map the firm into 5NF tables.

When I consider how we are still, for a little while longer at least (nano nano) following Moore's law, I think Mr. Dave is right. Normalize to the 5NF, unless your firm's needs require beyond 5NF, and monitor usage for any potential
denormalization needs.

Every time I read something to the effect of: "yes, but all of those joins will slow the DB down....".

I think to myself.... "OK get faster machines. I have to assume we have all of these joins, because the firm is size-able, large, at least in transaction size and hopefully has the ability to invest in faster hardware."

So problem solved. Please, DO NOT try to sell me on the idea that too many joins will slow the system down.

I am interested in some one describing a real world example which contradicts my idea that too many joins can be solved by faster hardware or more processing power, arrays of machines.

Of course the example can stay anonymous, no actual company names are necessary.

On a message thread somewhere, someone wrote something to the effect of the following:

Youtube and Facebook maintain large well indexed tables and essentially DO NOT normalize.

AND I cannot sleep, literally. It is unbelievable to me that the fore mentioned web sites do not normalize their tables.

Thanks in advance for your comments.

From what little I have seen of Pinal before this, in my opinion he is lacking in common sense.

But you seem to have already made up your mind, so go ahead with 5th normal form and enjoy the many hours you will spend coding and maintaining it.

We, here at dbforums, eagerly await the pleas for assistance that are sure to originate from whatever unlucky sap has to maintain your application after you are gone.

I personally thought he gave the game away by making references to the most recent TWIT received or sent on Twitter.

Wait a minute....where does Pinal recommend 5th normal form?
And what do you mean by "unless your firm's needs require beyond 5NF"?
Are you confusing 3rd normal and 5th normal?

lexcola,

Clearly the main advantages of a Fifth Normal Form database over a denormalized one are that it eliminates many common problems caused by redundancy, including update anomalies and redundant logic in code.

Also a normalized schema reduces any "bias" in the design and makes it more suitable for changes as requirements evolve - obviously very important for iterative and Agile projects (in fact normalization has been called one of the "secret assumptions" of Agile).

Regarding performance. You have to look at each particular scenario with its indexing, storage, data and queries. Normalization has nothing to do with performance because it tells us nothing about how the data will be physically stored and accessed. Of course it would be possible to contrive an example to prove that one implementation performs faster or slower under certain conditions but that won't tell you how your database will perform.

With that in mind though, using Normal Form with all the advantages I just described is very likely to give you the best chance to get good performance from your database overall. Simply by avoiding redundant data and code, avoiding biased designs and reducing the work needed to maintain integrity you will be doing everything right to get the best out of the DBMS you are using.

It goes past third????

...or else it gets the hose again.

got it short and sweet, thanks gents

What makes you say this?

pootle flump ur codings are working excelent.

Schadenfreude. The perverse sense of pleasure derived from saying "I told you so".

Ok - so why do you think a database normalise to 5th normal form require more code and maintenance?

If your database is not in fifth normal form and you want to ensure there can never be any update anomalies then you need to write some triggers. If it is in fifth normal form then you don't.

pootle flump ur codings are working excelent.

Exactly. You'll need more lines of code if the database is denormalized. You'll create more headaches for application developers. You'll also spend much more time refactoring code as the data model changes because you'll have to update some things multiple times.

Denormalization violates the principle of DRY. If you want to protect yourself against change, support good development practice or use iterative development methods then Normal Form really is an essential discipline. There's not much excuse for not doing it.

I was equating fifth-normal form with EAV. I stand corrected.

IMHO, things are not as clear as you say. In my experience, the normalization process *alone* leads to develop terribly slow gas plants, requiring much useless work from applications programs. Hopely, they're able to get the job done, but sometimes even not… I have many examples of this.
Because databases designers applied blindly their favorite theory, believing naively this is enough. Open your eyes: database communication with application program is an issue which cannot be solved ONLY creating normalized databases.Agile programmers speak of an "impedance mismatch" (an understatement, I think).
I'm both an application and database designer, and to me, standing fiercly on relationnal model or on object model, ignoring or deprecating the other one is equally stupid. To me, the real problem is getting the best of the two, and this means often trade-offs and compromissions.
Regards
Laurent

Agreed. I certainly didn't mean to imply that Normalization is all you need. Good application design and OO design is just as important.