# Maybe Normalizing Isn't Normal

**URL:** <https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290>\
**Category:** blog\
**Created:** [July 14, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290 "2008-07-14T00:00:00Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![codinghorror](https://discourse-cdn.codinghorror.com/user_avatar/discourse.codinghorror.com/codinghorror/32/36371_2.png) [@codinghorror](https://discourse.codinghorror.com/u/codinghorror)\
**Post date:** [July 14, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/1 "2008-07-14T00:00:00Z")

</div>

One of the items we're struggling with now on [Stack Overflow](http://stackoverflow.com) is how to maintain near-instantaneous performance levels in a relational database as the amount of data increases. More specifically, how to [scale our tagging system](http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html). Traditional database design principles tell you that well-designed databases are always [normalized](http://en.wikipedia.org/wiki/Database_normalization), but I'm not so sure.

* * *
This is a companion discussion topic for the original blog entry at: [http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html](http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html)

---

<div class="post-metadata">

**Author:** ![Odd](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/o/f9ae1b/32.png) [@Odd](https://discourse.codinghorror.com/u/Odd)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/2 "2008-07-15T00:00:00Z")

</div>

Just to add that materialized view will involves caching of the data.

---

<div class="post-metadata">

**Author:** ![Rick](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/r/9f8e36/32.png) [@Rick](https://discourse.codinghorror.com/u/Rick)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/3 "2008-07-15T00:00:00Z")

</div>

View data isn’t cacbed, however the execution plans are.

Personally I thnk anyone who encourages denormalization to the extent that this article does hasn’t worked with databases enough. Denormanlzation should always be an optimization, and not done by design, and a denormanlized database is _not_ the same as database that was never normalized to begin with.

In many case I think materialized / indexed views will suffice in cases when you think denormalization is necessary.

---

<div class="post-metadata">

**Author:** ![rogueDBA](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/r/3bc359/32.png) [@rogueDBA](https://discourse.codinghorror.com/u/rogueDBA)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/4 "2008-07-15T00:00:00Z")

</div>

You changed the rules.

The normalized model allows for n number of user phone numbers, screen names, affiliation and work history. It also does not care about the order of those items.

The de-normalized model limits the number of the items listed above. It also force an order.

Only one represent the business. Which is it?

---

<div class="post-metadata">

**Author:** ![Phil](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/p/e95f7d/32.png) [@Phil](https://discourse.codinghorror.com/u/Phil)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/5 "2008-07-15T00:00:00Z")

</div>

I didn’t realise that denormalisation was controversial. I was taught to do it in university.

---

<div class="post-metadata">

**Author:** ![Tero](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/t/e47774/32.png) [@Tero](https://discourse.codinghorror.com/u/Tero)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/6 "2008-07-15T00:00:00Z")

</div>

If you are just mostly doing dirty reads and do not particularly care if the data is 100% consistent all the time then you gain little by doing a lot of normalization. In fact, you should denormalize as much as it makes sense to do so for read-heavy loads.

Most web applications are of this type and do not even require a regular database necessarily. We are just conditioned to throw that database there every time without even questioning it. That is pretty high in the brainless zombie score rating.

---

<div class="post-metadata">

**Author:** ![Steve](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/s/f19dbf/32.png) [@Steve](https://discourse.codinghorror.com/u/Steve)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/7 "2008-07-15T00:00:00Z")

</div>

great post. simple is beautiful…

---

<div class="post-metadata">

**Author:** ![RobertF](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/r/a4c791/32.png) [@RobertF](https://discourse.codinghorror.com/u/RobertF)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/8 "2008-07-15T00:00:00Z")

</div>

Denormalized data makes a mess of the application code: you end up having to do all kinds of funny work to make sure that their affiliation is added to the right spot (affiliation\_1? affiliation\_2?), and if you remove it, then you end up backtracking and rectifying how your assignments work. And that’s assuming that you don’t accidentally run into a case where someone has 4 affiliations.

This whole concept is physically painful for me to even consider.

Before you start denormalizing your database, you should talk to a DBA and see what solutions they might have. And step #2 would be to be smarter about pulling back data than slurping back the entire database via your ORM.

Between those two options, you should be able to get the DB performance you’re looking for without hemorrhaging functionality and extensibility.

---

<div class="post-metadata">

**Author:** ![Reader\_101](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/r/a587f6/32.png) [@Reader\_101](https://discourse.codinghorror.com/u/Reader_101)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/9 "2008-07-15T00:00:00Z")

</div>

I just spent a year having normalization hammered into my meager little mind as part of my database module and now you’re telling me that it mightn’t be necessary…

love your blog Jeff but i hate it when you do this to me. ☹

---

<div class="post-metadata">

**Author:** ![AndrewS](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/a/898d66/32.png) [@AndrewS](https://discourse.codinghorror.com/u/AndrewS)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/10 "2008-07-15T00:00:00Z")

</div>

How about using materialized query tables or materialized views?

---

<div class="post-metadata">

**Author:** ![JuanZ](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/j/bbce88/32.png) [@JuanZ](https://discourse.codinghorror.com/u/JuanZ)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/11 "2008-07-15T00:00:00Z")

</div>

@Raymond Berg: I agree but I don’t think Google can resolve a link in the form of [http://www.codinghorror.com/mtype/mt-comments-renamed.cgi?\_\_mode=redid=xxxx](http://www.codinghorror.com/mtype/mt-comments-renamed.cgi?__mode=redid=xxxx) to an actual site.

---

<div class="post-metadata">

**Author:** ![SomeGuy](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/s/ac8455/32.png) [@SomeGuy](https://discourse.codinghorror.com/u/SomeGuy)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/12 "2008-07-15T00:00:00Z")

</div>

My approach is to design it all normalized at first, as it makes the design cleaner and usually easier to grok, and then denormalize where absolutely needed. As long as the system is performing well, I would prefer to keep my data normalized and then create denormalized views so I can get those huge joins out of my way.

When it comes to teaching, normalized tables is the way to go. People must master the rules before they break them (and at that point nobody will have to teach them that denormalization is OK, they will have figured that out already)

---

<div class="post-metadata">

**Author:** ![nub](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/n/ccd318/32.png) [@nub](https://discourse.codinghorror.com/u/nub)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/13 "2008-07-15T00:00:00Z")

</div>

Quite a horrible job naming the tables. Why the underscores in field names and not in table names. Why user\_id and not user\_sex? Why table nimes in singular? Of course this is beside the point, but lol… 😃

---

<div class="post-metadata">

**Author:** ![Rob](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/r/838e76/32.png) [@Rob](https://discourse.codinghorror.com/u/Rob)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/14 "2008-07-15T00:00:00Z")

</div>

Jeff, IMO, more important than denormalization is how you design your indexes, and what type of hardware you’re running on. IO is the big killer, not so much normalization. Denormalizing a table would be about the last thing I would try to boost performance. SQl server should be able to shred those queries, even with 20 million plus records in each table.

---

<div class="post-metadata">

**Author:** ![PeterB](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/p/e8c25b/32.png) [@PeterB](https://discourse.codinghorror.com/u/PeterB)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/15 "2008-07-15T00:00:00Z")

</div>

Normalization is about design, denormalization is about optimization. They should be applied as such, i.e. anything should be normalized until denormalization is needed. As Knuth (or was it Hoare?) said: Premature optimization is the root of all evil.

And from the short glimpse I did at the article about performance of tag systems it seems they tested only on MySQL. Not exactly a database one connects with good performance on complex queries – it’s extremely fast on flat data, but to my (not utterly up-to-date) knowledge they never managed to catch up with Postgres or the commercial RDBMS’ on anything reasonably complex. Not sure if that applies to single joins already, but I wouldn’t be too surprised.

---

<div class="post-metadata">

**Author:** ![Jens\_Gring](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/j/c0e974/32.png) [@Jens\_Gring](https://discourse.codinghorror.com/u/Jens_Gring)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/16 "2008-07-15T00:00:00Z")

</div>

Normalization might indeed not be the fastest design, but can usually made fast enough without denormalization, as Dare Obasanjo states in his original article, too:

Database denormalization is the kind of performance optimization that should be carried out as a last resort after trying things like creating database indexes, using SQL views and implementing application specific in-memory caching.

---

<div class="post-metadata">

**Author:** ![WeAreJimbo](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/w/13edae/32.png) [@WeAreJimbo](https://discourse.codinghorror.com/u/WeAreJimbo)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/17 "2008-07-15T00:00:00Z")

</div>

In my **LONG** experience of both DB and application design, it is a bad (or rushed) application design that usually forces denormalization on a well designed database.

Several small queries over normalized data also usually outstrip one big one over denormalized data.

But, denormalization still has a home in large data warehouses.

---

<div class="post-metadata">

**Author:** ![Lex\_Y\_\_Li](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/l/58f4c7/32.png) [@Lex\_Y\_\_Li](https://discourse.codinghorror.com/u/Lex_Y__Li)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/18 "2008-07-15T00:00:00Z")

</div>

Hope smart tools from DatabaseGear can help.

---

<div class="post-metadata">

**Author:** ![Christof](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/c/3da27b/32.png) [@Christof](https://discourse.codinghorror.com/u/Christof)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/19 "2008-07-15T00:00:00Z")

</div>

Lets unroll our loops next.

---

<div class="post-metadata">

**Author:** ![ICR10](https://discourse-cdn.codinghorror.com/letter_avatar_proxy/v2/letter/i/cdc98d/32.png) [@ICR10](https://discourse.codinghorror.com/u/ICR10)\
**Post date:** [July 15, 2008, 12:00am UTC](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290/20 "2008-07-15T00:00:00Z")

</div>

Just a nitpick, I’d recommend using the term Gender over Sex. Someone might ally themselves with a different gender to their sex and that’s the one they’re going to want to tell you.  
(Obviously it’s sample data, but just for the future 🙂

Id did annoy me slightly always having normalization drummed into me without mention that it’s not always best, but I guess SomeGuy is right.

[Next page](https://discourse.codinghorror.com/t/maybe-normalizing-isnt-normal/290.md?page=2)