3 ms·
Normalized means free[er] of redundancies. If you have a shopping basket, then you need the user it belongs to, the products in it and their quantities. Everyth
by danbruc 3y ago
Normalized means free[er] of redundancies. If you have a shopping basket, then you need the user it belongs to, the products in it and their quantities. Everything else like shipping costs and total costs can be derived from that. If you explicitly store the total costs because it is faster to show a value you already have instead of calculating it from the product prices and the default delivery address each time you need it, then you have a denormalized data model. It is now faster for the common operation of showing the total costs of a shopping basket but at the cost that you now have to invalidate shopping cart total costs when you change the price of a product or else the total costs might not reflect the current price of all the products. In a certain sense denormalization is just a form of caching and come with all the same problems as other caches, most notably of course cache invalidation. Materialization - as in materialized database views - is an mechanism to automatically denormalize your data that [hopefully] also ensures that the materialized data is always up to date and consistent with the rest of the data.
- taeric 3y agoRight, but you could just have a materialized view of the data, as well. We don't typically call that denormalized, but it is roughly the same thing. A trap with a lot of denormalizations, is that they allow you to edit the data that was otherwise materialized. Right?
- ralusek 3y agoA materialized view is absolutely demoralized. The only distinction between something like a materialized view, an index, and just ad hoc representation of the same data in multiple places is that with an index and materialized view, it's explicitly considered to be a read only reflection of some underlying data, whereas some other structure is more likely to have common pitfalls such as not properly having a single source of truth.
- taeric 3y agoI love the typo of demoralized. :D But, yeah. My question for the thread is if that is considered in this case or not? I'm assuming the poorly done denormalizations are of the editable kind?
- antonvs 3y agoDepends what you mean by editable. Even though a materialized view may not be editable, many of its underlying values may be, and that's the root cause of the problem with denormalization. Denormalized data effectively involves caching some source value in a durable way. As the saying tells us, cache invalidation is one of the two hard problems in computer science. Of course in the denormalized DB context, detecting invalidation is typically simple: it's assumed to occur as soon as an update of a denormalized value occurs somewhere. At that point you may need an ACID update of all "cached" values, which is conceptually and computationally expensive. In the materialized view case, you may not do an update, but then you have an out of date cache. So, we do see the effects of denormalization in a materialized view, but the pros and cons are a little different than in the case where data is stored in a primary table.
- taeric 3y agoI mean editable in the view. That you may have to cascade an edit out, makes sense. But, that fits how things are physically, too. Any store that puts prices on items will have a similar physical constraint that they are under.
- Izkata 3y agoY'know, a materialized view has to be updated regularly... Could we call an out-of-date one demoralized, like, lost confidence?
- danbruc 3y agoI added something about materialization while you replied. Materialization is controlled automatic denormalization. I would guess it is usually not labeled as denormalization because the materialized data is not really part of your data model, it is more a cache of precomputed data that is automatically kept up to date and consistent. In contrast denormalization in the data model proper usually means that the code has to ensure that things remain consistent.
- taeric 3y agoI got confused, as I thought you meant you edited the linked article. :D So, yeah, this all makes sense. My assertion for it being more like reality was not that all data is denormalized. But if you have things like "color" or "configuration" of item normalized out so that the "shopping cart" is purely product ID listings, than that is just as inaccurate in other ways. That said, it sounds like we agree?
- danbruc 3y agoBut if you have things like "color" or "configuration" of item normalized out so that the "shopping cart" is purely product ID listings, than that is just as inaccurate in other ways. Here I would disagree. Normalization can not become inaccurate, you just keep getting rid of more and more redundancies the further you push your normalization efforts. If you have a product with different color options, then you probably need a two level model, one for the product and one for its variants. If you are selling computers and allow configuring them, it might become quite complex with different options for different aspects, there might be constraints like incompatible options or options that require others, the price might depend non-trivially on the different choices and so on. So in practice the complexity of really normalizing everything and then piecing everything back together in code is probably what will eventually stop you from going after the very last redundancies.
- taeric 3y agoI don't want us to just hash over silly examples that are easy to nitpick. My point is that if you have an inventory, you don't have normalized items. You have "realized" items that have, in fact, copied out the colors and other design choices out in a way that cannot cascade on edit. Stated differently, if the data can always cascade out with editing, than yeah, it should probably be normalized. If it is a design choice that gets fixed on application, it can be argued that it should be copied out when it can't be changed.