Your thoughts on Denormalization?

[Mark1inLA]

Hello all,

 

I wanted to get your thoughts on denormalization.

 

In 1 hand you have a performance boost by not having to use joins and the other you have data integrity and no redundancy.

 

Are there special circumstances where you would sacrifice integrity over performance?

 

Have you had any experiences denormalizing and do you have any strong opinions either way?

 

[corbin]

Always normalize unless you have a significant reason not to do so.  Denormalization usually will not have better performance by the way.

[Mark1inLA]

Always normalize unless you have a significant reason not to do so.  Denormalization usually will not have better performance by the way.

 

I agree with you. Denormalization goes against the philosophy of putting integrity in equal footing with performance.

 

1 particular article brings up some valid points that support this practice, but I still have a tough time grasping this as a viable long-term solution.

[corbin]

I didn't read the entire article since I need to go do something (I do plan to read the entire thing though), but from the first 2 pages or so though, it appears that the author has hit or miss accuracy on what he's saying.

 

 

Also, I find it hard to take seriously anyone who writes this PHP code:

 

 

$Result = mysql_query("SELECT sum(amount) FROM transactions where customer_id=$customer_id AND type like '%deposit%'");
$Row = @mysql_fetch_row($Result);
$ltv = $Row[0];

 

 

It's also worth noting that that query could be much better done such that it wouldn't have to be run for each and every customer_id.

 

 

Based on those two things, it's hard to take the article for truth, but once I read the whole thing, maybe I'll change my mind.

 

 

 

 

 

 

By the way, I'm not saying database normalization doesn't cause a performance hit in some situations.  I'm just saying that 95% of the time, normalizing data is the better way to go.  There are definitely situations where it's OK to not normalize data though.

[fenway]

Denormalization is really only required for seriously large databases with crazy load requirements.