Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

I stopped worrying and learned to love denormalized tables

glean.io

Post reply on HN
Print view View original

61–70 of 100 posts

Re: I stopped worrying and learned to love denormalized tables

#61

Post by cmrdporcupine »

brightball wrote: post #13
MilStdJunkie wrote: post #4

For the third time this week, in relatively unrelated fields of computation science, I'm reminded of the quote: "Duplication is less expensive than the wrong abstraction". An awful lot of the time, a table schema is a terrible abstraction of the actual series it is designed to record. Sometimes it's designed under constraints that exist only to self-sustain the abstraction. Some of them have viable reasoning, some do…

The schemas always exist, it’s just a question of where: the database or the code that interacts with the database.

The other way of putting it is this: the database gives you the chance to express facts (propositions) about the world in the form of tuples in relations ("rows" in "tables".)

You can, naturally, also express facts any other way you like. Comments in this forum, scribbles on the bathroom walls, seven layers of JavaScript buried in 10 microservices.

But a relational DB gives you the tools to at least attempt the expression of those propositions in a way that makes them findable and provable later, with minimal pain, and by people across the organization, not just in one code base.

Doing it in code, your mileage may vary.

And just like in written or spoken language communication, we can express facts poorly or incorrectly in any medium.

It's just that the relational model gives us a tool and framework to help put discipline (and query flexibility) into that process. If we use the tool right, it helps us clarify our logic.

Re: I stopped worrying and learned to love denormalized tables

#62

Post by andyjohnson0 »

I've worked in early-stage startup environments where we didn't always have the time or resources to build proper management tools, and editing the database sometimes was the management tool. I'm not proud of this but thats how it was.

In this situation, denormalised tables are much easier to hand edit than tables that have been normalised out into the eighth dimension and beyond.

Re: I stopped worrying and learned to love denormalized tables

#63

Post by MichaelMoser123 »

Data denormalization makes sense if the data is written once and never updated - like with a data warehouse / analytics.

If you need to update the data then denormalization can turn into a big source of trouble. For example you end up with many copies of the same stuff, and you must make an extra effort to update all the duplicates upon update.

Or you end up with multiple entries, where the validity of an entry is determined by some extra 'isValid == true' or 'deleted == false' field. Now all these 'invalid' entries then start to clog up the table/collection, and performance may quitely deteriorate.

I once had to use a denormalized schema for nested data, as lookup through too many reference would have suffered. But that wasn't funny at all.

Re: I stopped worrying and learned to love denormalized tables

#64

Post by OJFord »

makeitdouble wrote: post #5

It sounds like the author is calling a kind of materialized/persisted view "denormalized tables". The actual DB tables stay untouched and fully normalized. It sure makes sense to love them, views are great. I don't know why they need a new name. > Transformation tools such as dbt (Data Build Tool) have revolutionized the management and maintenance of denormalized tables. With dbt, we can establish clear relationships…

There was me assuming it was about normal form, you know, what everyone else means when they talk about 'normalising' in a database context...

Which even more confusingly almost applies but the opposite way around:

> create denormalized analytics datasets on top of them

A common pattern is to have source data 'warehoused' or whatever you want to call it, and then build your, er, normalised schema on top of that.

Re: I stopped worrying and learned to love denormalized tables

#65

Post by BulgarianIdiot »

grzm wrote: post #60
BulgarianIdiot wrote: post #59

Earlier quoted context omitted.

Who decided the point of using databases is normalization? Where is that coming from? Relational databases have existed before the concept of normalization existed. Also an index is nothing more than a partial copy of a table with a different key. It denormalizes you data. Do you use indexes other than pk?

> Who decided the point of using databases is normalization? Where is that coming from? Relational databases have existed before the concept of normalization existed. Ted Codd, the guy who wrote A Relational Model for Large Shared Data Banks , the paper that introduced the relational model. The first section is entitled “Relational Model and Normal Form” https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://e…

If we simply mark denormalized data as internal representation or an index, then denormalized won’t exist and everyone will be happy.

Re: I stopped worrying and learned to love denormalized tables

#66

Post by xg15 »

roenxi wrote: post #12

Well... 1) Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it. That isn't complicated. Denormalised data sometimes points at design flaws, but even then all systems have design flaws and they don't automatically need to be fixed. Quality is expensive, like every other property (even doing things the cheap way is expensive, ironically - software is all about managing costs). 2) For…

I find it interesting how the argument of the current top comment[1] - that denormalized tables are great, as long as you never have any updates for existing entities - basically follows directly from your point 2) :

If for any individual use case a denormalized table fit specifically to that usecase is superior to a normalized table, the most straightforward solution for multiple use cases would be to just keep multiple tables and replicate any insert on each of them. This would work relatively well until the moment where you have to update something...

[1] https://news.ycombinator.com/item?id=35927226

Re: I stopped worrying and learned to love denormalized tables

#67

Post by xg15 »

OT (or maybe not) : It's interesting how the idiom "I stopped worrying and learned to love X" today is taken at face value and is basically a plea to accept something seemingly insane and just go with it - when in the original movie, the person making the statement was genuinely insane and the movie's entire message was basically the opposite.

Re: I stopped worrying and learned to love denormalized tables

#68

Post by p4bl0 »

MichaelMoser123 wrote: post #63

Data denormalization makes sense if the data is written once and never updated - like with a data warehouse / analytics. If you need to update the data then denormalization can turn into a big source of trouble. For example you end up with many copies of the same stuff, and you must make an extra effort to update all the duplicates upon update. Or you end up with multiple entries, where the validity of an entry is de…

I'd argue that most of the time it is better to write normalized data and use some form of permanently existing database views (rather than actual table) to read from pseudo-denormalized data. That way you combine the best of both worlds.

Re: I stopped worrying and learned to love denormalized tables

#69

Post by sclarisse »

p4bl0 wrote: post #68
MichaelMoser123 wrote: post #63

Data denormalization makes sense if the data is written once and never updated - like with a data warehouse / analytics. If you need to update the data then denormalization can turn into a big source of trouble. For example you end up with many copies of the same stuff, and you must make an extra effort to update all the duplicates upon update. Or you end up with multiple entries, where the validity of an entry is de…

I'd argue that most of the time it is better to write normalized data and use some form of permanently existing database views (rather than actual table) to read from pseudo-denormalized data. That way you combine the best of both worlds.

That really, really depends on whether you’re driving something that gets read often enough that query complexity ruins your product’s ability to predictably deliver on query deadlines — whether that’s “load a webpage” deadline or a “submit half a million payments to the bank before it closes” deadline.

Re: I stopped worrying and learned to love denormalized tables

#70

Post by mmcnl »

sublinear wrote: post #24
hakunin wrote: post #6

While this talks mostly about data warehousing, oftentimes denormalization is useful for everyday web app data storage. If your web app (usually on Postgres) is mostly frequent reads and rare writes (most web apps are) — there's no excuse for your pages to load slower than a static site. Store your data as normalized as you want, add a denormalized materialized view, update it on writes, render pages based on the vie…

> there's no excuse for your pages to load slower than a static site. Indeed all web pages should be static pages. Anyone still doing server-side rendering in 2023 and defending it for any use case at all needs to turn in their badge. Same goes for people promoting frameworks like react or vue for anything but sufficiently complex web apps.

Strong statements without any substance behind them like yours are not helpful in my opinion.

So how about something a bit more nuanced: choose the right tool for the right job.

Post reply on HN