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

11–20 of 100 posts

Re: I stopped worrying and learned to love denormalized tables

#11

Post by teej »

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…

It’s complicated. Databases have entities called “views” and “materialized views” that have a specific meaning in that ecosystem. dbt let’s you define views in the abstract sense, but they’re implemented using a variety of different database primitives, like tables, temp tables, common table expressions, and views. dbt calls them “models”.

Re: I stopped worrying and learned to love denormalized tables

#12

Post by roenxi »

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 any given user it is better to have denormalised data where the data model is perfectly aligned to their use case. For a system with multiple users it is better to have normalised data. And the corollary is that any data important enough to be recorded is probably valuable enough that it will eventually have multiple interested users even if the person building the system swears that this time is different - so they should normalise their data. Brownie point to anyone who has reached enlightenment and understands the you of 12 months hence is a different user with different needs of the data.

Re: I stopped worrying and learned to love denormalized tables

#13

Post by brightball »

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.

Re: I stopped worrying and learned to love denormalized tables

#14

Post by SnowHill9902 »

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 world is always 6NF.

Re: I stopped worrying and learned to love denormalized tables

#15

Post by hn_throawlles »

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…

> It sure makes sense to love them, views are great. I don't know why they need a new name.

in very many cases, old things get new names so that more people can share in the claim that they invented what has been in fact merely re-invented

Re: I stopped worrying and learned to love denormalized tables

#16

Post by alex_lav »

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.

If your app/architecture is effectively "BYO Schema", if one schema is wrong the other's aren't necessarily, and the cost of making a mistake is much lower. And I would also argue that even if your database is noramlized and has a strict schema, the code _still_ has the ability to implement its own schema after pulling data out.

Re: I stopped worrying and learned to love denormalized tables

#17

Post by PeeMcGee »

teej wrote: post #11
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…

It’s complicated. Databases have entities called “views” and “materialized views” that have a specific meaning in that ecosystem. dbt let’s you define views in the abstract sense, but they’re implemented using a variety of different database primitives, like tables, temp tables, common table expressions, and views. dbt calls them “models”.

Sounds like a typical workflow in any BI tool. I wish the author would have taken a moment to explain how Glean is any different.

Re: I stopped worrying and learned to love denormalized tables

#18

Post by Spivak »

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.

I'm not entirely sure how true that actually rings because it's true by definition in some sense but having tables just be loose collection of typed nullable columns sans natural (maybe compound) primary keys is a very different experience from the kind of thing you expect. It's a schema in the way @dataclass is a schema and just ends up being a place to put stuff. It leads to very very few joins being necessary, natural keys map well to most problem domains, and foreign key constraints feel almost unnecessary since your real data is the key it's really hard to "lose" relationships.

Re: I stopped worrying and learned to love denormalized tables

#19

Post by BoiledCabbage »

Spivak wrote: post #18
brightball wrote: post #13

Earlier quoted context omitted.

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

I'm not entirely sure how true that actually rings because it's true by definition in some sense but having tables just be loose collection of typed nullable columns sans natural (maybe compound) primary keys is a very different experience from the kind of thing you expect. It's a schema in the way @dataclass is a schema and just ends up being a place to put stuff. It leads to very very few joins being necessary, nat…

It feels to me like you're mixing the terms schema and relations/joins.

You can have a schema on a table without any foreign keys or relations. But in the above you seems to use those concepts interchangeably.

Taking OPs comments directly about schemas, it seems true to me.

Re: I stopped worrying and learned to love denormalized tables

#20

Post by noduerme »

Okay, so someone who analyzes reads, who's never written software that needs consistent writes, is in favor of denormalized data. Let's see how this post updates in ten years with parts 4 thru 9 where they go from realizing their data is inconsistent to writing some monstrous beast to try to normalize it.
Post reply on HN