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".
I stopped worrying and learned to love denormalized tables
51–60 of 100 posts
Re: I stopped worrying and learned to love denormalized tables
#52For 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…
That's not what the article is about but the question of what level of abstraction and how flexible it needs to be is always interesting.
On table schemas, I think designers will usually have a good idea of how stable it needs to be and what should go in it. For instance someone creating an invoice table will probably already have a set of unvariable stuff that need to go there for sheer legal reasons.
The other part being, an unefficient or slightly clunky table schemas is not the end of the world and can either be fixed, even in pretty active production envs (it's "just" that much more effort and cost intensive) or be dealt with at the application layer.
Stacking abstractions is always an option if nothing else helps, and I'd see trying denormalized at the very start of a new application a worse tradeoff and lack of thinking about what the application is supposed to do at its core.
Re: I stopped worrying and learned to love denormalized tables
#53As 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.
Re: I stopped worrying and learned to love denormalized tables
#54Well... 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 m…
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.
Re: I stopped worrying and learned to love denormalized tables
#55How do you deal with multiple cardinality in a denormalized table? Using Arrays?
Re: I stopped worrying and learned to love denormalized tables
#56You 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…
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
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-...
Re: I stopped worrying and learned to love denormalized tables
#57Why 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.
Re: I stopped worrying and learned to love denormalized tables
#58Indexing 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.
Re: I stopped worrying and learned to love denormalized tables
#59Why 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?
Re: I stopped worrying and learned to love denormalized tables
#60Why 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?
> 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://en.wikipedia.org/wiki/Relational_model
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.