Normalization is from the devil | Hacker News Reader

Normalization is from the devil

ayende.com

7 pointssuraj11 comments

If you think about it, normalization in RDBMS had such a major role because storage was expensive

Umm, not really. Two words: referential integrity.

The devil you know is better than the devil you don't.

Every un-normalized database I've inherited has been a colossal mess and practically unusable -- especially if there are important relations. A de-normalized database is a thoughtful process which I rarely see a developer do -- they just want to get programming and don't think about long-term issues.

The particular scenario in the article is one in which denormalizing would be not only more efficient, but more correct as well. As the author points out, an invoice should always reflect the order at the time that it was created, not including any subsequent changes in pricing. However, this is strictly a matter of correctness! In order to denormalize, it should either be demonstrably more correct (in which case it is not technically denormalization at all), or a significant performance bottleneck where the cost of maintaining two copies is overshadowed by the savings. Doing so in the name of performance without any proof that there's savings to be had is folly.

Normalization minimizes the chance of update anomalies. Removing inconsistencies doesn't sound very devlish.

Why was the line between Customers and Orders removed? Adding CustomerID to Orders just does the same thing.

All they've done is remove the connection between Products and OrderLines, which is partially rational in the real world: what if you want to give a discount? A partial refund? What if your products change prices at times? If you don't include the cost on the OrderLine, you've gotta handle version issues for every single instance.

As to the product name... why would it change? A new company took over the product? I'll bet it has a different cost, new packaging, and a new description. Is it really the same product? And, by disconnecting them, they've now lost any ability to do comparative analysis, recommendations, num-sold... anything meaningful aside from displaying the order.

If your only goal is display, just save the rendered webpage you presented them. It has as much meaning, and will always look the same. If it's doing anything with your data, normalize away.

Something like this will work (ActiveRecord): Customer.find(:all, :include => {:orders => {:order_lines => :product}})

Pretty trivial, and loads all the needed records in a single query.

Correct me if I'm wrong, but I'm pretty sure ActiveRecord will issue a separate query for each of those :include clauses.

http://api.rubyonrails.org/classes/ActiveRecord/Associations...

> This will load all comments with a single query. This reduces the total number of queries to 3. More generally the number of queries will be 1 plus the number of associations named (except if some of the associations are polymorphic belongs_to - see below).

Good call. Still, the difference between 1 and 3 queries is nothing compared to 3 and 100s (n+1 problem).

I wonder what AR can't turn that into a single query.

Oh you're right about that. What may not be entirely obvious though is that oftentimes 1 query can be far slower than 3 queries. In a lot of cases, that one query would have to do joins against multiple tables– I've personally seen speedups of 20x or more by just decomposing a single complex query into a few smaller and more focused queries.

Always normalize a relational database. Denormalize caches.

Normalization has nothing to do with storage or performance, it has everything to do with modelling the problem correctly. The point about a product's name changing or a person's name changing is valid, but the solution there is to keep revisions in the DB when it's important.

Reply on news.ycombinator.com