Hacker News Reader

Comment by rudd | Hacker News Reader

It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.

View on HN

Yes, you're right. I agree that there are times that denormalizing makes sense. My point was that the item said:

* Throw away the normal forms you learned in school

* Denormalize [...] whenever it makes a query faster.

I think that's bad advice as stated. As you imply, you have to know why the rules are there to know when to break them intelligently. I didn't get that message from the item as posted, rather that the author thought that NF had little value, which probably doesn't belong in a list of database tips and tricks.

[edit: format]

Its bad advice because people denormalize the wrong tables. They'll actually slow queries down by denormalizing a table with a few rows into a table with many rows - which makes things slower.

Been unplugged all day so haven't been around to respond. This is a completely fair criticism - the wording in my post is indeed too plain. Without understanding the normal forms making smart decisions about denormalization is going to be very difficult.

I think "don't worry about normality" is a ridiculously bad statement. But perhaps "selectively denormalize when you run out of other optimzations" would be a little more reasonable.

One approach is to make all your denormalizations populated by triggers -- that way to you can still think about the DB in "clean" terms in your code when modifying data, but get your denormalized tables for queries, too.

The difference is in a matter of degree. I agree that denormalisation can be beneficial - even necessary - in certain scenarios, particularly when other design optimisations are exhausted. But that's a far cry from "the normal forms you learned about in school are an utter waste of time that will throw your database performance in the gutter."

Agreed it was too strong of a statement, but denormalization really is something you do all the time in big databases. It is not some targeted last resort technique.

If you have 30 million rows in a table you absolutely cannot do joins so you bring everything in that you commonly need.

Agreed.

Most of the serious commercial databases I've worked with/near/on have been de-normalised to some extent.

e.g. SalesForce run massive amounts of data. Their implementation really boils down to one de-normalised table; with a dozen or so supporting tables.

When I hear about some (not all) NoSQL implementations I occasionally wonder why they just didn't adopt a de-normalised SQL solution.

Because if you're going to do that - why not drop the SQL and the ORM altogether? Most shops at that level aren't doing analysis on their production SQL anyway - they dump that into a data warehouse. So why not do your CRUD on NoSQL and dump that some place ppl can run SQL on it?

We do it the other way round to your suggestion.

SQL Server + NHibernate does handle the CRUD stuff, custom fields and DDD-based domain logic very well.

NoSQL (CouchDB in our case) doesn't handle transactions, locking and business rules effectively. However it excels at providing extremely fast views on schemaless data such as our core domain model plus custom fields.

Definitely interesting :)

Because often that's going too far. Quite often you still need transactions, established technologies, some structured-data, , etc, etc.. Sometimes a regular SQL database, but with some de-normalised tweaks is all you need.

There is definitely a place for NoSQL. However, many projects simply don't need to bite this off. They could start with SQL, from there you can decide how rigid/structured you want to be... I definitely agree that this comes with baggage; but if you're prepared to be pragmatic it's often got a lot of upside.

Reply on news.ycombinator.com