Orlin Monad's Library tagged item →  View Popular

Werner Vogels (Amazon's CTO) mentioned that you can consider denormalizing entities to get better scalability and performance

in addition to the "no need to normalize immutable data", you don't even need to delete

Another [idea] is that you actually don't need to delete/update the database. 'Deleting' a row means setting a InvalidationTimestamp = now(), and updating a row means setting InvalidationTimestamp = now() and inserting a new row with SinceTimestamp = now() and InvalidationTimestamp = null (you actually need two sets of dates, but that's for another post). Now, if you put the two ideas together, all the data is immutable, so you don't need to normalize anything.

databases are not ready for the large number of columns per table such a scenario will generate, which is solvable if you apply archiving to older data

"Database denormalization is the kind of performance optimization that should be carried out as a last resort after trying things like creating database indexes, using SQL views and implementing application specific in-memory caching. "

when you need massive scale you probably need to denormalize

Normalised data is for sissies

in Flickr's case there is a ration of 13 Selects to each Insert/Delete or Update which is why they chose to denormalize

"people normalize 'cuz their professor said to"

It all depends heavily on the nature of your application. In some cases normalization is critical. OLTP (online transaction processing) systems with large numbers of updates seem to benefit most from normalization. Having one copy of the data - and just referencing it via foreign key can be a huge help for maintaining consistency; each copy of that data that needs to be updated increases the chance that the values will diverge. Joins may be slower - but most modern database servers provide "views" - allowing you to create flattened tables, yet still maintain relational integrity.

On the other hand you have a class of applications that tend to have many more reads than writes. Often those are what you would term OLAP (Online Analysis and Processing) applications. Major database vendors have been pushing "Data Warehouse" solutions for some time - flattening out your data to improve complex query processing: tabulating sales figures by region and quarter for instance.

Smart money seems to be on hybrid systems and building it smart. Your "online transaction" system being fully relational for performance reasons... and replicating that data to a warehouse flattening out the data for your online analysis needs. The OLTP system can purge out old inactive records based on timed retirement - with "backup" of the data now being stored in the OLAP system.

Some of the larger web sites that have rethought normalization have done so because their problem space typically does not require it. Data accuracy at flickr isn't exactly of paramount importance (compared to financial systems). Not to mention flickr still seems to have abysmal performance in spite of claims to the contrary.

I wouldn't say normalization is for sissies. I would say you should understand why or why not to normalize. It's also possible that the process of breaking down your data set - normalizing it - will help you understand the shape of your data better; even if you don't ultimately end up storing it in a normalized schema.

I need to run batches of statistical queries over a normalized database, but it is horribly inefficient. I can only spare minutes, but the queries take better part of a day to run over a normalized database, if not days in some extreme cases. On the other hand, I can't really denormalize the existing database and it would be a horrible choice for many other things we do.

So, we are keeping a mirrored copy that squeezes all the tables into a handful denormalized ones. It is literally thousands of times faster for the queries we need. And that's what we use it for - just for queries that need to be extremely fast. And sometimes that is too slow as well, so we are looking at partitioned views, to limit the queries to smaller chunks of data. Beyond that we are really looking at physical partitioning over several servers.

a more accurate term is database normalization

Normalization is part of a much larger concept that some people call "DRY" or "Once and Only Once." Normalization actually makes my application code SIMPLER in many cases.

I have seen denormalization cause absolute havoc with application code, much less issues with updates.

The primary reason for having 'normalised' database tables is the problem of redundancy ... the perfect rule is that every fact in the database should be represented once, and once only. Redundancy introduces the problem of having to update the same fact everywhere it is recorded in the database. There are, however, many good reasons to denormalise. But what makes this an allowable and useful practice is the fact that by denormalising, you clearly are fully aware of the redundancy that you are creating, and your assumedly will maintain update control over that. The real problem of denormalised data is where the designer is really unaware of it, and / or the developers in the future are unaware of it, with the obvious potential for the redundantly recorded facts to become incorrect and invalid.

I am sure that certain facts in any database are in reality immutable, thus creating no problems when redundantly recorded. But always control must be maintained over this redundancy, as in ensuring that future generations of users and developers are aware of this.

I think the best approach is to create a stringently normalised data model, and then carefully and with great forethought undertake a denormalisation activity.

Un-normalized is a structure that has never been normalized.

The performance justification for de-normalized data is easy to verify - just test the model or do the calculations at the relative create, read, update, delete frequencies you expect.