10 ms·

I stopped worrying and learned to love denormalized tables

108 points by vndrewlee 3y ago

berkle4455 3y ago
Every time someone praises dbt, a view sheds another tear.
- williamjackson 3y ago
  Honest question: if I want to apply IaC to my views, is there a better tool than dbt to take care of that for me? I have never used dbt before, only read about it.
- dopidopHN 3y ago
  I use view everyday but what is a dbt ? If I have to install binaries locally that sound boring
  - ghilston 3y ago
    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.
    - dopidopHN 3y ago
      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
MilStdJunkie 3y ago
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 don't. How these structures sustain themselves for . . decades . . is a mystery to me. These non-relational movements, in part represented by the OP article, are (in part) attempts to shift the computing from data to the actual programmatic area. Because the real world doesn't have schemas - although that's still, incredibly, a source of intense disagreement. Just an interesting thing that keeps cropping up. I wonder what the formal, "scientific" name for this is?
- zh217 3y ago
  Maybe "Impedance Mismatch"?
  - MilStdJunkie 3y ago
    That's perfect! The electrical metaphor is a powerful one, as evidenced that it effortlessly describes a sister of the OP problem, "Object-Relational Impedance Mismatch". Looking at the most compact expression of the problem - the electrical one, i.e. math - you start to wonder if the root cause of all these is scale(observer) vs speed vs signal. Could it be expressed as a logical abstraction to this family of phenomenon: impedance matching; object-relational impedance; business system vs ERP? For every "reference frame" (electrical, mechanical, software, database, system) an organization node (single developer, team, organization) might be in, there would be a sort of minimum beyond which no unit is discernible. As this unit grows, the risk of "impedance mismatch" grows, even if signal and velocity remain static. If signal and velocity ALSO grow, the probability of mismatch rapidly becomes 100%. Unlike in electronics, the actual physical size of the "carrier wave" is getting bigger[1]. Which, honestly, ok, this all sounds pretty damn obvious. Maybe that's why this is a solved problem in EE, but it's a forty-year-clusterpoop in ERP world. Could it be that the root cause, then, is nontechnical leadership? A PoliSci MS / MBA won't - or can't - see that larger systems necessarily have different signalling / flow, but they "think they can pull this off" because "airplanes and lawnmowers are basically the same thing" and "our culture is always our first product". Blop. Fail. Repeat for two generations, and here we all are. [1] Which, hmmph, ok, that can happen in some specialized setups. But that's outside this sandbox.
- brightball 3y ago
  The schemas always exist, it’s just a question of where: the database or the code that interacts with the database.
  - alex_lav 3y ago
    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.
makeitdouble 3y ago
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 between table abstractions, create denormalized analytics datasets on top of them, and ensure data integrity and consistency with tests.
- binkHN 3y ago
  Fully concur with this! It succinctly sums up the article! There really isn't much meat there!
- teej 3y ago
  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”.
  - PeeMcGee 3y ago
    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.
  - roncesvalles 3y ago
    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.
- hn_throawlles 3y ago
  > 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
hakunin 3y ago
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 view. Of course I'm talking about 95% of the apps where this is acceptable, not 5% where table locking can cause problems, leading to the need for concurrent update handling.
- smegsicle 3y ago
  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
  - doodlesdev 3y ago
    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
    - selcuka 3y ago
      - Knock knock - Race condition - Who's there?
- sublinear 3y ago
  > 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.
redsaz 3y ago
It's important to note that the author uses denormalized tables for data analysis only. It was never outright stated "don't do this for source-of-truth, authoritative data," but yeah, don't do this for source-of-truth, authoritative data (in general).
roenxi 3y ago
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.
- vbezhenar 3y ago
  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.
  - namaria 3y ago
    As usual, context matters and decontextualized discussions often devolve into people shouting past each other. Sometimes you can't afford to do it right, quick and dirty is the way. Sometimes you can't afford not to do it right. It all depends heavily on how costs and payoffs are distributed socially and temporally. The real trick is to be doing what fits your situation at the moment, and knowing how your situation might change over time.
noduerme 3y ago
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.
- myrryr 3y ago
  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 many years later.
  - datathrow0007 3y ago
    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 access. Likewise, SQL is not conducive to thorough testing coverage; and migration management is prone to user-error. In this vein, the industry has once again innovated and revolutionized data-access, moving away from "CREATE READ UPDATE DELETE" to "ENCAPSULATE INSTANTIATE INJECT CREATE READ(+UPDATE|DELETE)* TRACK PUSH SYNC." With all this in-mind, it would be utterly baroque to use anything other than JS+Node+Mongo+Mongoose for a fully unified front-end and back-end. *An astute reader will recognize that ORMs are constantly doing N+1 queries (to first pull the data, transform it into objects, update said objects, and then push the changes to the database). We feel as though hardware has gotten sufficiently advanced that the mind-space these costs inhabit is no longer justified -- and it is OK to do things this way. Right-side: ORMs suck, just write SQL
    - myrryr 3y ago
gigatexal 3y ago
Yeah for an OLTP system until you hit certain scale normalized is fine and actually really needed. For OLAP reporting and analysis denormalized is the way to go for sure. Reducing the number of joins needed (preferably to 0) makes things go very fast.
samtho 3y ago
Given that initially defining your db schema is a one-time thing and we generally don’t change it very often after, I can’t fully get behind what the article suggests. However, the one thing I tend to do is add a text or json field called “extra” to my main tables that just stores a JSON map with fields I want to add to the record but don’t need to necessarily query by.
tlarkworthy 3y ago
You have to denormalize in very common cases even for mostly OLTP workloads in order to get sortable data into an index. Consider a cloud storage product with folders connecting with a many to one to documents. The product wants to display the most recently used folders ordered by their inner document modification date. Because composite indexes commonly can't span tables, you have to push the last modification date up to the folder row in order to get the data in the right place to build the obvious index. Denormalization is a normal and expected optimization to scale a relational database.
- slavveras 3y ago
  Couldn't you also use a materialised view here? Or, alternatively, what about an index on the documents table alone? It might depend on whether your db has a flexible enough indexing system to do what you want, but I don't see why the index would have to span tables since it only needs to depend on the folder id and the document modification time
  - tlarkworthy 3y ago
    A materialized view is another approach but thats still essentially denormalization. I prefer using indeces as they are a bit more of a 1st class relational concept in my mind. With a MV you are copying ALL table data, but with an index you can concentrate on just the ordering columns (you can make a few column MV too and then join but you are now reinventing indexes). You cannot get an index to be used across a join in most relational DB system. But joins pop up everywhere when you normalize. This query is a fairly distilled example of it https://stackoverflow.com/questions/16402225/index-spanning-multiple-tables-in-postgresql https://stackoverflow.com/questions/16402225/index-spanning-...
therufa 3y ago
Why isn't the author just using a document store instead? This entire post is defeating the purpose of RDBMS'. What happens when data needs to be updated? Would that require a number of n update statements in order to change a username for instance?
revskill 3y ago
What is dbt ? How's it related to denormalized tables ? The article is a bit confusing to me. What're alternatives ?
fbn79 3y ago
Denormalized table is just an hard to mantain (materialized)view over normalized tables.
nottorp 3y ago
Hmm as far as I can remember that's what they told us at uni. After hammering normal forms into our heads for a while they added "and you can carefully denormalize for speed".
sergioisidoro 3y ago
As someone who today has to maintain a database with a lot of denormalised data, do this only if your database is pretty much write once (the author's use case). For anything else you might feel that it's saving you time and performance by not needing to join tables, but you're just shooting yourself in the foot with a delayed effect. You're just moving complexity from the read to the write operation with a multiplication effect.
lolive 3y ago
How do you deal with multiple cardinality in a denormalized table? Using Arrays?
pk-protect-ai 3y ago
Why not use first normal form instead of fully denormalized tables? What is a point using RDBMS if you do not need the normalization? Denormalized tables are only good for sequential scans, you will screw up the DB performance if you need update, insert operations on such tables. And if you do not need updates/inserts then you do not really need RDBMS. And you will definitely screw the DB performance if you permanently scan denormalized tables.
- BulgarianIdiot 3y ago
  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?
  - grzm 3y ago
    > 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://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization Projections (think views) and indexes (which are generally on-disk projections) are not disallowed. The point is that the logical representation is normalized. The logical representation and the on-disk or in-memory representations are not the same thing. There’s nothing in the logical model from preventing you from having different representations for performance or other reasons. This has been obscured somewhat by the common implementations of relational databases where the on-disk and logical representations are often the same, which tends to make people think they have to be the same. That’s not the case.
    - BulgarianIdiot 3y ago
BulgarianIdiot 3y ago
Indexing is a form of denormalization. It rekeys the table from another vantage point to enable optimal different queries. So the presence of denormalized data reveals shortcoming of the indexing capabilities of a system. An index is a projection that is guaranteed synced with the source of truth. And debirmalization of data are all projections.
andyjohnson0 3y ago
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.
MichaelMoser123 3y ago
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.
- p4bl0 3y ago
  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.
  - sclarisse 3y ago
    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.
  - shagmin 3y ago
    I've started doing this (materialized views denormalizing data) at work more recently and it's been immensely helpful. Surprised more people don't do this.
- mejutoco 3y ago
  Data denormalization also helps with restoring individual tables and sharding. IMO one should aim for normalization and slowly denormalize only if needed.
xg15 3y ago
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.
civilized 3y ago
At my company we have normalized data warehouses, and data scientists query them to make big, static, denormalized tables to run through their analyses and models. I thought this was a pretty standard, table stakes move in the analytics world.
billy_bitchtits 3y ago
dimensional modeling seems to be a waste of time in many cases, when the end user just wants a flat table they can drop into excel or into a data frame to do some eda/modeling. if it makes it easier for the data warehouse team to build a kimball model and then put views on top of it to deliver the flat tables, or it works better to build a kimball model because you're going to set powerbi on top of it, fine. for most data analysts, data scientists, etc. stopping at a dimensional model just leaves them needing to put the pieces together themselves.
erikig 3y ago
A wizened Perl guru once told me “Normalization is an optimization not a rule…”
at_a_remove 3y ago
Denormalized tables are like bedrooms: the mess is fine if it is your own mess, not so great if it is someone else's. Right now I have some data with both MUNI and MUNICODE. I just thought I'd casually check and sure enough, I have a non-trivial number of cases where the two do not match up right. So, the risk there is ... which one do I believe?
goto11 3y ago
Normalization only applies to the base tables, i.e. the tables which work as the "single source of truth". Queries and views (which are just stored and possibly cached queries) often have repeated information and this is not a problem. A data-warehouse is basically a cached query over the base tables, so again, normalization is not an issue. The article seem to agree with this, but it is kind of buried in the text.
vrglvrglvrgl 3y ago
[dead]
SPBS 3y ago
This post is about data analysis i.e. read-only queries. Of course denormalization wouldn't hurt, and might speed things up. Normalization is about making writes easier, because you only have a single source of truth to update. Try writing into a database that has a denormalized schema... ugh. > Denormalized tables prioritize performance and simplicity, allowing data redundancy and duplicate info for faster queries. By embracing denormalization, we can create efficient, maintainable data models that promote insightful analysis. Yes, for read-only queries. Again, your business should not be storing data into denormalized tables. Store them in normalized tables and pull the data out into denormalized tables for data analysis.

108 points by vndrewlee 3y ago

berkle4455 3y ago

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

williamjackson 3y ago
Honest question: if I want to apply IaC to my views, is there a better tool than dbt to take care of that for me? I have never used dbt before, only read about it.
dopidopHN 3y ago
I use view everyday but what is a dbt ? If I have to install binaries locally that sound boring
- ghilston 3y ago
  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.
  - dopidopHN 3y ago
    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

MilStdJunkie 3y ago

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 don't. How these structures sustain themselves for . . decades . . is a mystery to me. These non-relational movements, in part represented by the OP article, are (in part) attempts to shift the computing from data to the actual programmatic area. Because the real world doesn't have schemas - although that's still, incredibly, a source of intense disagreement. Just an interesting thing that keeps cropping up. I wonder what the formal, "scientific" name for this is?

zh217 3y ago
Maybe "Impedance Mismatch"?
- MilStdJunkie 3y ago
  That's perfect! The electrical metaphor is a powerful one, as evidenced that it effortlessly describes a sister of the OP problem, "Object-Relational Impedance Mismatch". Looking at the most compact expression of the problem - the electrical one, i.e. math - you start to wonder if the root cause of all these is scale(observer) vs speed vs signal. Could it be expressed as a logical abstraction to this family of phenomenon: impedance matching; object-relational impedance; business system vs ERP? For every "reference frame" (electrical, mechanical, software, database, system) an organization node (single developer, team, organization) might be in, there would be a sort of minimum beyond which no unit is discernible. As this unit grows, the risk of "impedance mismatch" grows, even if signal and velocity remain static. If signal and velocity ALSO grow, the probability of mismatch rapidly becomes 100%. Unlike in electronics, the actual physical size of the "carrier wave" is getting bigger[1]. Which, honestly, ok, this all sounds pretty damn obvious. Maybe that's why this is a solved problem in EE, but it's a forty-year-clusterpoop in ERP world. Could it be that the root cause, then, is nontechnical leadership? A PoliSci MS / MBA won't - or can't - see that larger systems necessarily have different signalling / flow, but they "think they can pull this off" because "airplanes and lawnmowers are basically the same thing" and "our culture is always our first product". Blop. Fail. Repeat for two generations, and here we all are. [1] Which, hmmph, ok, that can happen in some specialized setups. But that's outside this sandbox.
brightball 3y ago
The schemas always exist, it’s just a question of where: the database or the code that interacts with the database.
- alex_lav 3y ago
  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.

makeitdouble 3y ago

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 between table abstractions, create denormalized analytics datasets on top of them, and ensure data integrity and consistency with tests.

binkHN 3y ago
Fully concur with this! It succinctly sums up the article! There really isn't much meat there!
teej 3y ago
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”.
- PeeMcGee 3y ago
  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.
- roncesvalles 3y ago
  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.
hn_throawlles 3y ago
> 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

hakunin 3y ago

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 view. Of course I'm talking about 95% of the apps where this is acceptable, not 5% where table locking can cause problems, leading to the need for concurrent update handling.

smegsicle 3y ago
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
- doodlesdev 3y ago
  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
  - selcuka 3y ago
    - Knock knock - Race condition - Who's there?
sublinear 3y ago
> 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.

redsaz 3y ago

It's important to note that the author uses denormalized tables for data analysis only. It was never outright stated "don't do this for source-of-truth, authoritative data," but yeah, don't do this for source-of-truth, authoritative data (in general).

roenxi 3y ago

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.

vbezhenar 3y ago
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.
- namaria 3y ago
  As usual, context matters and decontextualized discussions often devolve into people shouting past each other. Sometimes you can't afford to do it right, quick and dirty is the way. Sometimes you can't afford not to do it right. It all depends heavily on how costs and payoffs are distributed socially and temporally. The real trick is to be doing what fits your situation at the moment, and knowing how your situation might change over time.

noduerme 3y ago

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.

myrryr 3y ago
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 many years later.
- datathrow0007 3y ago
  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 access. Likewise, SQL is not conducive to thorough testing coverage; and migration management is prone to user-error. In this vein, the industry has once again innovated and revolutionized data-access, moving away from "CREATE READ UPDATE DELETE" to "ENCAPSULATE INSTANTIATE INJECT CREATE READ(+UPDATE|DELETE)* TRACK PUSH SYNC." With all this in-mind, it would be utterly baroque to use anything other than JS+Node+Mongo+Mongoose for a fully unified front-end and back-end. *An astute reader will recognize that ORMs are constantly doing N+1 queries (to first pull the data, transform it into objects, update said objects, and then push the changes to the database). We feel as though hardware has gotten sufficiently advanced that the mind-space these costs inhabit is no longer justified -- and it is OK to do things this way. Right-side: ORMs suck, just write SQL
  - myrryr 3y ago

gigatexal 3y ago

Yeah for an OLTP system until you hit certain scale normalized is fine and actually really needed. For OLAP reporting and analysis denormalized is the way to go for sure. Reducing the number of joins needed (preferably to 0) makes things go very fast.

samtho 3y ago

Given that initially defining your db schema is a one-time thing and we generally don’t change it very often after, I can’t fully get behind what the article suggests. However, the one thing I tend to do is add a text or json field called “extra” to my main tables that just stores a JSON map with fields I want to add to the record but don’t need to necessarily query by.

tlarkworthy 3y ago

You have to denormalize in very common cases even for mostly OLTP workloads in order to get sortable data into an index. Consider a cloud storage product with folders connecting with a many to one to documents. The product wants to display the most recently used folders ordered by their inner document modification date. Because composite indexes commonly can't span tables, you have to push the last modification date up to the folder row in order to get the data in the right place to build the obvious index. Denormalization is a normal and expected optimization to scale a relational database.

slavveras 3y ago
Couldn't you also use a materialised view here? Or, alternatively, what about an index on the documents table alone? It might depend on whether your db has a flexible enough indexing system to do what you want, but I don't see why the index would have to span tables since it only needs to depend on the folder id and the document modification time
- tlarkworthy 3y ago
  A materialized view is another approach but thats still essentially denormalization. I prefer using indeces as they are a bit more of a 1st class relational concept in my mind. With a MV you are copying ALL table data, but with an index you can concentrate on just the ordering columns (you can make a few column MV too and then join but you are now reinventing indexes). You cannot get an index to be used across a join in most relational DB system. But joins pop up everywhere when you normalize. This query is a fairly distilled example of it https://stackoverflow.com/questions/16402225/index-spanning-multiple-tables-in-postgresql https://stackoverflow.com/questions/16402225/index-spanning-...

therufa 3y ago

Why isn't the author just using a document store instead? This entire post is defeating the purpose of RDBMS'. What happens when data needs to be updated? Would that require a number of n update statements in order to change a username for instance?

revskill 3y ago

What is dbt ? How's it related to denormalized tables ? The article is a bit confusing to me. What're alternatives ?

fbn79 3y ago

Denormalized table is just an hard to mantain (materialized)view over normalized tables.

nottorp 3y ago

Hmm as far as I can remember that's what they told us at uni. After hammering normal forms into our heads for a while they added "and you can carefully denormalize for speed".

sergioisidoro 3y ago

As someone who today has to maintain a database with a lot of denormalised data, do this only if your database is pretty much write once (the author's use case). For anything else you might feel that it's saving you time and performance by not needing to join tables, but you're just shooting yourself in the foot with a delayed effect. You're just moving complexity from the read to the write operation with a multiplication effect.

lolive 3y ago

How do you deal with multiple cardinality in a denormalized table? Using Arrays?

pk-protect-ai 3y ago

Why not use first normal form instead of fully denormalized tables? What is a point using RDBMS if you do not need the normalization? Denormalized tables are only good for sequential scans, you will screw up the DB performance if you need update, insert operations on such tables. And if you do not need updates/inserts then you do not really need RDBMS. And you will definitely screw the DB performance if you permanently scan denormalized tables.

BulgarianIdiot 3y ago
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?
- grzm 3y ago
  > 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://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Relational_model https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Database_normalization Projections (think views) and indexes (which are generally on-disk projections) are not disallowed. The point is that the logical representation is normalized. The logical representation and the on-disk or in-memory representations are not the same thing. There’s nothing in the logical model from preventing you from having different representations for performance or other reasons. This has been obscured somewhat by the common implementations of relational databases where the on-disk and logical representations are often the same, which tends to make people think they have to be the same. That’s not the case.
  - BulgarianIdiot 3y ago

BulgarianIdiot 3y ago

Indexing is a form of denormalization. It rekeys the table from another vantage point to enable optimal different queries. So the presence of denormalized data reveals shortcoming of the indexing capabilities of a system. An index is a projection that is guaranteed synced with the source of truth. And debirmalization of data are all projections.

andyjohnson0 3y ago

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.

MichaelMoser123 3y ago

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.

p4bl0 3y ago
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.
- sclarisse 3y ago
  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.
- shagmin 3y ago
  I've started doing this (materialized views denormalizing data) at work more recently and it's been immensely helpful. Surprised more people don't do this.
mejutoco 3y ago
Data denormalization also helps with restoring individual tables and sharding. IMO one should aim for normalization and slowly denormalize only if needed.

xg15 3y ago

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.

civilized 3y ago

At my company we have normalized data warehouses, and data scientists query them to make big, static, denormalized tables to run through their analyses and models. I thought this was a pretty standard, table stakes move in the analytics world.

billy_bitchtits 3y ago

dimensional modeling seems to be a waste of time in many cases, when the end user just wants a flat table they can drop into excel or into a data frame to do some eda/modeling. if it makes it easier for the data warehouse team to build a kimball model and then put views on top of it to deliver the flat tables, or it works better to build a kimball model because you're going to set powerbi on top of it, fine. for most data analysts, data scientists, etc. stopping at a dimensional model just leaves them needing to put the pieces together themselves.

erikig 3y ago

A wizened Perl guru once told me “Normalization is an optimization not a rule…”

at_a_remove 3y ago

Denormalized tables are like bedrooms: the mess is fine if it is your own mess, not so great if it is someone else's. Right now I have some data with both MUNI and MUNICODE. I just thought I'd casually check and sure enough, I have a non-trivial number of cases where the two do not match up right. So, the risk there is ... which one do I believe?

goto11 3y ago

Normalization only applies to the base tables, i.e. the tables which work as the "single source of truth". Queries and views (which are just stored and possibly cached queries) often have repeated information and this is not a problem. A data-warehouse is basically a cached query over the base tables, so again, normalization is not an issue. The article seem to agree with this, but it is kind of buried in the text.

vrglvrglvrgl 3y ago

[dead]

SPBS 3y ago

This post is about data analysis i.e. read-only queries. Of course denormalization wouldn't hurt, and might speed things up. Normalization is about making writes easier, because you only have a single source of truth to update. Try writing into a database that has a denormalized schema... ugh. > Denormalized tables prioritize performance and simplicity, allowing data redundancy and duplicate info for faster queries. By embracing denormalization, we can create efficient, maintainable data models that promote insightful analysis. Yes, for read-only queries. Again, your business should not be storing data into denormalized tables. Store them in normalized tables and pull the data out into denormalized tables for data analysis.