81–90 of 100 posts
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?
The article seem to agree with this, but it is kind of buried in the text.
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 permanent…
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?
The relational model distinguish between the logical and physical levels. Normalization applies to the logical level. At the physical level you can have duplicate data, e.g. caches, which is fine as long as the engine guarantee consistency of the logical model.
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.
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…
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
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…
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 the minds of the people using the database.
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.
> 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.
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…
Maybe "Impedance Mismatch"?
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.
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 don't see why you are making the distinction by single user or multi user. If you are writing information into the database, it has to be written into a normalized schema. There is no alternative, multi user or not. To do otherwise would be to maintain multiple sources of truth and nothing in the database would make sense if two different sources of data go out of sync. Make illegal states unrepresentable.
Also if you want to align your data model perfectly to a user you might as well use a single-table database like DynamoDB [1] where everything is screaming fast but the downside is you can't modify your schema at all (because it's already set in stone to perfectly model the old use case).