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

21–30 of 100 posts

Re: I stopped worrying and learned to love denormalized tables

#21

Post by noduerme »

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 real world doesn't have schemas

This is simply incorrect. The real world has business logic, and that's ironclad (or should be, for a successful business, assuming you're not acquired at which point start the business data schema over). Where business logic has exceptions, those exceptions should be accommodated in the schema. But there should be no daylight between how the business works and how the schema records its every function. Anything else is bad design. It works for decades only if a business makes its decisions partially based on how those are constrained by prior logic, and the DNA of that logic is the schema.

Re: I stopped worrying and learned to love denormalized tables

#22

Post by smegsicle »

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…

i think i heard once that there are only a few problems in computer science, including off-by-one errors, and concurrent update handling

oh yeah and overengineering, probably

Re: I stopped worrying and learned to love denormalized tables

#23

Post by doodlesdev »

smegsicle wrote: post #22
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…

i think i heard once that there are only a few problems in computer science, including off-by-one errors, and concurrent update handling oh yeah and overengineering, probably

Concurrency.""There are three hard things in computer science: cache invalidation, naming things, off-by-one errors, and

Original quote:

   There are only two hard things in computer science: cache invalidation and naming things. — Phil Karlton
The one that most people know:

   There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors. - Jeff Atwood

Re: I stopped worrying and learned to love denormalized tables

#24

Post by sublinear »

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.

Re: I stopped worrying and learned to love denormalized tables

#26

Post by vbezhenar »

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…

Regarding quality being expensive.

It's not only about cost to implement. There's also cost to change.

If your isolated module is bad, you can rewrite the code, keeping API the same. Cost to change is not high. You might introduce new bugs and that's about it.

Changing database structure might be hard. Adding new checks might require manual fixes to already bad data or multiple code paths for old and new data. Some migrations might require putting system offline. Often you can't just rollback your changes if things went wrong after few days.

Changing API with hundreds of customer... Good luck with that.

Changing POSIX API at this moment probably just not possible.

Whenever something is hard to change, quality requirements are naturally higher.

For data model quality requirements should be high. More time you spend, more time will be saved later. As we say: we're not so rich to buy cheap things. We're not so rich to afford poor DB schemas.

Re: I stopped worrying and learned to love denormalized tables

#27

Post by MilStdJunkie »

noduerme wrote: post #21
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 real world doesn't have schemas This is simply incorrect. The real world has business logic, and that's ironclad (or should be, for a successful business, assuming you're not acquired at which point start the business data schema over). Where business logic has exceptions, those exceptions should be accommodated in the schema. But there should be no daylight between how the business works and how the schema rec…

In twenty years of "ERP Whispering" I've never known a "formal" "schema" (going to put "schema" in quotes here too, what that actually represents is a strictly validating structure, or system of any kind) that described a business object as utilized (process, reference, part number database) with anything greater than, say, forty percent commonality. At the high end. The remainder is waived, "ad-hocced", or is simply fibbed ("oh sure, all our parts have registered NSNs[1]").

The systems are, at their best, an executive class (VPs, Senior Mgmt) contracting a shaman/priestly class (me) to lay a sheet of order on throbbing chaos.

I probably should mention that it's possible - likely, even - that I have not worked for a non-dysfunctional business, and of course the ERP software ecosystem introduces its own peculiarities on top of that.

I realize I am badly abusing the word "schema" here. A lot of the time, we design schemas based on high level business requirements, and that's what I'm thinking about. There should be a LOT more between schema and business - a whole universe of business architecture, design, and software - but there never seems to be the money to do so.

In the wider context, though, the phenomenological world, I would posit, does not have business logic inherent in the fact of its own existence. This could get to be a very spatious, "dude-wheres-my-bong" sort of discussion, but I think the difference in Weltanschauung we're seeing here might be due to divergent experience.

[1] NATO Stock Numbers

Re: I stopped worrying and learned to love denormalized tables

#28

Post by Fire-Dragon-DoL »

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 kinda observe this from a different point of view: what are the use cases needed for the data and what are the queries that could satisfy those?

The dynamodb book was enlightening from this point of view even for designing sql databases where we normalize data as much as possible no matter what.

Re: I stopped worrying and learned to love denormalized tables

#29

Post by roncesvalles »

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.

The whole SSR revival thing in frontend right now seems to exist only to game SEO and has no benefit for the user.

Re: I stopped worrying and learned to love denormalized tables

#30

Post by roncesvalles »

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”.

The debate over normalized/denormalized has to do with how authoritative online data should be stored. What you do with derived datasets is not really contentious; do whatever you want.
Post reply on HN