7 ms·

Normalization is from the devil

7 points by suraj 16y ago

michael_dorfman 16y ago
If you think about it, normalization in RDBMS had such a major role because storage was expensive Umm, not really. Two words: referential integrity.
toddh 16y ago
Normalization minimizes the chance of update anomalies. Removing inconsistencies doesn't sound very devlish.
jkmcf 16y ago
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.
adelevie 16y ago
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.
- texel 16y ago
  Correct me if I'm wrong, but I'm pretty sure ActiveRecord will issue a separate query for each of those :include clauses.
  - adelevie 16y ago
    http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html 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.
    - texel 16y ago
      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.
gxti 16y ago
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.
Groxx 16y ago
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.
nwmcsween 16y ago
Always normalize a relational database. Denormalize caches.
bhiggins 16y ago
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.

7 points by suraj 16y ago

If you think about it, normalization in RDBMS had such a major role because storage was expensive Umm, not really. Two words: referential integrity.

toddh 16y ago

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

jkmcf 16y ago

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.

adelevie 16y ago

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.

texel 16y ago
Correct me if I'm wrong, but I'm pretty sure ActiveRecord will issue a separate query for each of those :include clauses.
- adelevie 16y ago
  http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html 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.
  - texel 16y ago
    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.

gxti 16y ago

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.

Groxx 16y ago

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.

nwmcsween 16y ago

Always normalize a relational database. Denormalize caches.

bhiggins 16y ago

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.