4 ms·

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 c

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.

epalmer 10y ago
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.
- nsxwolf 10y ago
  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.
- knucklesandwich 10y ago
  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.
scaryclam 10y ago
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.
- knucklesandwich 10y ago
  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.
deleted 10y ago
[deleted]
mslot 10y ago
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.
- knucklesandwich 10y ago
  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.

epalmer 10y ago

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.

nsxwolf 10y ago
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.
knucklesandwich 10y ago
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.

scaryclam 10y ago

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.

knucklesandwich 10y ago
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.

deleted 10y ago

[deleted]

mslot 10y ago

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.

knucklesandwich 10y ago
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.