Hacker News Reader

Comment by knucklesandwich | Hacker News Reader

IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.

View on HN

I think his example was rather poor as I can think of at least 3 use cases off of the top of my head where having separate categories would be a good idea, and having varchar arrays would be a giant PITA.

That said I'm not sure he was trying to make the point that upfront normalisation is bad in itself, just that doing too much "what if maybe someday we might want to separate this" kind of normalisation is a waste of time.

Granted. In my experience wildly changing requirements are more common than unexpected need to scale though. "Design for the most likely needs" is my general and unspecific advice. YMMV.

Only as a general rule: I have found that write once read mostly tables (history tables) are great candidates for denormalizing. State tables (read & write) usually benefit from 3rd form normalization.

Right, which I never understood until the first time I was tasked with building a data warehousing and reporting application. My naive 3NF schema approach led to joins that crippled report performance, and really messy schemes to deal with underlying changes in the application's schema over time.

Agreed, I should have made that clear. Anything log-like (events stores, history, "checkins", etc. is excepted from this). Analytics in particular is a case where large collections are the norm, even with low user counts. I contend that it's exceedingly rare for a beginner to be in the position of having these kinds of considerations though.

But what if you need to partition and possibly scale out part of your database? At that point a normalized data model becomes a huge road block. A balanced approach to normalization often allows you to more adaptive to requirements. If possible, it should be avoided for tables that can grow large.

If you're in this position, you have scale informing product requirements already and can denormalize up front. But this is uncommon for read/write transactional workloads and an especially uncommon scenario for a beginner to find themself in.

Reply on news.ycombinator.com