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

91–100 of 100 posts

Re: I stopped worrying and learned to love denormalized tables

#91

Post by MilStdJunkie »

goto11 wrote: post #86
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 In the same sense that the real world doesn't have numbers, types, or functions. Platonist might disagree, but in any case it is beside the point. What matters is if these concepts are useful. There is no such thing as a schema-less database, the question is if the schema is formalized and stored and enforced in the database itself, or if it only exists implicitly in code or in t…

Hard agree on the computing system schema - it's always there, whether in a bag of brains or as a XSD file.

The usefulness of these concepts lie on a spectrum, with user behavior located in one part of that spectrum. Numbers occupies a broad spectrum that overlies most human experience, although not all. Type systems have overhead that occlude some aspects of computing, and by itself the phrase "type system" has fuzzy edges. Somewhat - although less so - same thing with functions - is a function a callable unit or is it an explicit method? Platonists - I think - would definitely agree that these things have the inherent quality of existence as a consequence of their nature; Anselm's Ontological Argument is sort of the Final Evolution of dire Platonism.

The world, though, as it exists in nature? Functions, types, even numbers are not primary observables. Our experience of reality is probably more akin to the observed experience of a rainbow, where the observer stands 138 degrees to the sun precisely . . or the rainbow doesn't exist. We're in a 138 degree arc to reality, living on a rainbow, counting the bands. But honestly? Who knows? Probably some learned cosmologist. I am but a MilStdJunkie on the internet.

Re: I stopped worrying and learned to love denormalized tables

#92

Post by myrryr »

datathrow0007 wrote: post #85
myrryr wrote: post #40

Earlier quoted context omitted.

This really does remind me of that meme with the bell curve. On the far left and right side, there is a person saying "denormalized data is great" It is just this one in the middle which doesn't like it. After doing pretty major projects for 40 years, let me tell you, "denormalized data is great" It is like watching people go down the hole of "patterns for everything" and then watching them crawl back out of it again…

A provocative spin: Left-side: ORMs suck, just write SQL Middle: ORMs relieve the impedance mismatch of the OOP "object graph" paradigm and the SQL "relational algebra" paradigm. One could even make the argument that this mismatch is inherent and we're just doing CPR on a rotting horse -- so now the industry standard is to use NoSQL databases, such as MongoDB, to get away from bygone ways of thinking about data acces…

The weird part here is you are assuming SQL was even the right thing to begin with.

But sure, if you want to use SQL, then yeah, I agree with you.

Re: I stopped worrying and learned to love denormalized tables

#93

Post by myrryr »

roncesvalles wrote: post #29
sublinear wrote: post #24

Earlier quoted context omitted.

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

There is real speed gains to be had.

Typically your server is right next to the database. The end user isn't, so being able to give their first view being right, without an extra trip around is pretty good.

Re: I stopped worrying and learned to love denormalized tables

#94

Post by roenxi »

datathrow0007 wrote: post #80
roenxi wrote: post #37

Earlier quoted context omitted.

In the case of this analyst, say every couple of days he is writing SELECT * FROM events JOIN projects. Everyone is asking him for information about events and projects. He's going to save time and make less mistakes if he uses DBT and has a denormalised tables that merges events and projects. However you can think of to define costs and benefits, it'll turn out to be a good choice. As he discovered in the article. G…

Use a view that acts as a quick-and-useful abstraction to mimic a denormalized table? E.g. ``` CREATE VIEW vw_events_and_projects AS SELECT * FROM events JOIN projects ``` Then ``` SELECT * FROM vw_events_and_projects ``` Edit: And if you need OLAP, replicate the normalized table to a database that handles analytics workflows better (e.g. ClickHouse). Then you get the normalized forms for your OLTP workflows (your "b…

You'd like DBT, you should go and read up on it. It uses views.

Re: I stopped worrying and learned to love denormalized tables

#95

Post by noduerme »

MilStdJunkie wrote: post #27
noduerme wrote: post #21

Earlier quoted context omitted.

> 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…

It's almost certainly down to divergent experience. The shamaning and priesting I've done has almost exclusively begun with small companies that were working out their business operations at the same time as they were commissioning software. As they grow, the business logic evolves and the software had to grapple with that, but the originally unified logic imposes constraints both ways. The software sets limits on, and is in conversation with, the whims of management... if for no other reason than that 10 years in, on version 72, the cost of changing the data schema may outweigh the hoped-for advantage of some off the cuff idea to change fundamental operating practices. More concretely, e.g. if a custom logistics system grows up with a company from the ground floor, the business logic tends to bend around the software's limitations [clarification: Employees learn to get things done that weren't originally anticipated, in ways that weren't envisioned]; then the software slowly incorporates the new needs into formal features. If you're dealing with rational management (like the original founders) they will see the sense in maintaining logical continuity and tweak operations as needed to accommodate what should or shouldn't be done in software. [As opposed to "in operations". It's when employees are repeatedly writing on white boards or passing papers around for the same thing that it needs to be integrated as a feature]. But telling the management that "changing this fundamental aspect is impossible without significant downtime" is often enough to start the conversation of how to shape it so that the schema and the business remain in lock step.

This isn't perfect. Ask Southwest airlines, who grew with their own software until they couldn't, and then switched catastrophically to an entirely new system. Sometimes things reach a scale of complexity that the software simply can't conform to. But a really good designer should see those things 2-3 years out and plan for them.

This is the real power of the shaman. I don't dictate business logic, but I do whisper when it contravenes the hard logic of the schema, I fight to minimize the number of exceptions ("hacks" of any kind) and through this keep the beast in check.

Re: I stopped worrying and learned to love denormalized tables

#96

Post by noduerme »

kqr wrote: post #36
noduerme wrote: post #21

Earlier quoted context omitted.

> 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…

I don't know what you mean by business logic but the (in my view successful) businesses I have seen operate more on the highly variable whim of upper management than any sort of ironclad logic. You notice this quickly when you computerise existing human processes. They are riddled with (sometimes very valuable!) inconsistencies that are hard to fit into the regular computer mold some designer thought was sensible.

I responded at length to the sibling but just want to say - usually where I've come in there is an operations manual that works very much like a schema. Investigating where it's being overridden in daily practice often helps to form more linear processes / more consistent logic than was in the manuals. Once those are worked out, the software becomes the glue that forces employees to follow the processes, and the manual is about the software. But you're right: Stepping into a messy organic system and writing software around it is hard. It's much harder if they aren't willing to be flexible.

Re: I stopped worrying and learned to love denormalized tables

#97

Post by goto11 »

roenxi wrote: post #37
kqr wrote: post #31

Earlier quoted context omitted.

> if the cost exceeds the value, then don't do it. That isn't complicated. Could you try to elaborate on what you mean by "cost" and "value"? The complicated part is not the part you said, but specifying those two very abstract terms.

In the case of this analyst, say every couple of days he is writing SELECT * FROM events JOIN projects. Everyone is asking him for information about events and projects. He's going to save time and make less mistakes if he uses DBT and has a denormalised tables that merges events and projects. However you can think of to define costs and benefits, it'll turn out to be a good choice. As he discovered in the article. G…

> Normalisation is there to make data accessible for multiple different users

Normalization is there to avoid anomalies which is another word for data corruption. If you have the same datum repeated multiple times in the base tables, any update can (and probably will, due to Murphys law) lead to inconsistencies, which mean the database is not a reliable source of information anymore. How do you quantify the cost of that?

> looking for opportunities to avoid writing the same JOIN in 20 different queries.

Then you define a view, with is literally just a named and reusable query, which can be used in other queries. Writing queries or using views is certainly not "denormalization". Having redundant data in a query or view output is commonplace and not a problem since it cannot introduce update-anomalies. (Some databases allow updateable views in some scenarios, but only when the view can be unambiguously mapped back to base tables, so no risk of update-anomalies here either.)

Re: I stopped worrying and learned to love denormalized tables

#98

Post by goto11 »

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…

> Normalisation at all costs is foolish - if the cost exceeds the value, then don't do it.

Value of denormalization: May improve query performance for some particular use cases. (But probably not any more than using a materialized view)

Cost of denormalization: Risk of anomalies (inconsistencies) which mean the database is not a reliable source of information anymore. Murphys law mean that this is basically bound to happen sooner or later. You should worry about this.

Just use a view.

Re: I stopped worrying and learned to love denormalized tables

#99

Post by ghilston »

dopidopHN wrote: post #8
berkle4455 wrote: post #2

Every time someone praises dbt, a view sheds another tear.

I use view everyday but what is a dbt ? If I have to install binaries locally that sound boring

Can you explain why that sounds boring?

I ask, as my preference would be to do the boring thing and install a binary locally. Like how one generally uses git for example.

Re: I stopped worrying and learned to love denormalized tables

#100

Post by dopidopHN »

ghilston wrote: post #99
dopidopHN wrote: post #8

Earlier quoted context omitted.

I use view everyday but what is a dbt ? If I have to install binaries locally that sound boring

Can you explain why that sounds boring? I ask, as my preference would be to do the boring thing and install a binary locally. Like how one generally uses git for example.

I meant in a SQL context. I don’t want to manage extra binaries to deploy by environments. Specially if the same result can be archived via standard SQL
Post reply on HN