when to denormalize database 1

[Rousseau10]

I read this following definition in an sql book.
WHat do real DB designers do to nromalize there database?
I am hoping for a real world example. TANX!!

definition of normalization:
A DB is considered to be normalized when every table contais info specific to only that table thereby reducing duplicate data. Some DBs make a choice to denormalize and store duplicate data so it can be read more quickly.

How would you go about storing dup data to be read more quckly?


I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[vongrunt]

Three steps, assuming database design from scratch:

1.) First normalize by the book up to 3NF or eventually BCNF.
2.) Identify possible hotspots and bottlenecks, then denormalize at these points
3.) Write extra routines (stored procs, triggers) that maintain integrity of denormalized data

Many people do all 3 steps at once (normalization "by experience") and usually miss/ignore something.

If you ask when/where to denormalize (step 2), here are some relatively common situations:

a) queries with WHERE clause over JOINed table (closest match to your example)
b) historical data (Products.Price may get changed, denormalize it in OrderDetails)
c) "last state" information (chronological TOP 1 - lastVisitDate in Members table)
d) totals of all kinds.

In all four cases, database contains redundant (and therefore denormalized) data that speeds up frequent queries.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[Rousseau10]

super answer, thanks so much!!!!