8 ms·
My first introduction to databases was with PHP/MySQL, where normalization was the name of the game. The whole point of normalization is that there is no duplic
by chatmasta 10y ago
My first introduction to databases was with PHP/MySQL, where normalization was the name of the game. The whole point of normalization is that there is no duplication of data anywhere. If it's possible for duplicate data to exist, that's a symptom of a design flaw in the schema.
I've been using Mongo recently, and every time I raise criticism of it, the counterargument I hear is "forget about normalization! Duplication is okay."
Like the author, I really cannot wrap my head around this. While I understand that duplicating documents across collections may make querying faster, what about when you want to change the document? You need to propagate the change across every duplicate of the document in every collection where it exists. This means that any "de-duplication" logic needs to happen at the application level, rather than the database level.
Parse.com provided an interesting abstraction around that with "cloud code" and beforeSave and afterSave triggers. To maintain consistency, they encourage propagating updates to a document within its collection's beforeSave function. So if a document changes in one collection, you write code to change all instances of the document in other collections. That's nice in that it almost feels like you're writing the dedeuplication logic in the database layer, because you can view the "beforeSave" and "afterSave" functions as extensions of the schema. As long as those functions are up to date, the schema and any pseudo-linked documents will stay up to date.
But I really don't buy it. The strengths of mongo encourage a design that necessitates complexity for any significant write operation.
I think the real issue is that "if you have a hammer, everything looks like a nail." Mongo and other NoSQL stores have some real use cases, but people who are more familiar with Mongo than RDBMS are too trigger happy to employ it as a solution to problems where a RDBMS is the clear solution.
- fiatjaf 10y agoIt has always seemed to me that the solution to the duplication/normalization dilemma is an approach that gets "normalized" data (not necessarily normalized in the sense that it is scattered among many tables, but in the sense that there is a single source of truth for each data point) and uses it to write materialized views, automatically and efficiently recomputed and stored for fast querying. CouchDB does this to a certain extent, but its JSON-document approach is not appropriate enough.
- overcast 10y agoHave a look at RethinkDB, it's the best of MongoDB and MySQL with real time feeds. A relational document store.
- deleted 10y ago[deleted]
- andybak 10y agoI've always looked on 'duplication' (or denormalization) as an optimization to be used sparingly when performance dictates. Regarding it as essentially a form of caching has been helpful. In fact there is a spectrum that goes from simple caching to denormalized schema with many points inbetween.
- phamilton 10y agoWhile not a fan of mongoDB for many reasons, any sufficiently large scale application will have denormalization. Caching layers cause all sorts of cache invalidation bugs because of this exact problem. Indexes, while often managed by the DB, are essentially denormalization. One could even argue that syncing data between a client and server is a class of the same problem. My point is that we deal with these problems out of necessity. If you don't get a ton of traffic, then sticking with a normalized RDBMS is probably best. However, large scale systems will have redundant logic. Generic solutions (letting the normalized DB do the work) will often be slower than specific ones (denormalizing your schema and moving logic into the application layer). These are pretty simple principles that I've seen on almost every large-scale application I've worked on.
- nurettin 10y agoIf you excuse the buzzword, data warehouses are built for this very reason. So you are right in the sense that cache invalidation and denormalization are problems in sql based large scale systems, but they are largely solved problems whereas in mongodb you would need to do this work manually.
- dozzie 10y ago> [...] data warehouses are built for this very reason. [...] [cache invalidation and denormalization] are largely solved problems [...] Uhm, no. Data warehouse is not a database with operational (i.e. current) data, it's a database with historical data. If it is out of sync with production for a day or a week, it's totally OK, depending on its design. So no, cache invalidation is not a solved problem there.
- manyxcxi 10y agoThis is a problem I've had to explain to too many developers recently. It's not faster just because you throw away data modeling practices and jam bits of info all over the place. If you have semi normalized data relationships, Mongo really doesn't fit the bill that well as your primary data store. Where it shines is when you've got computed pieces of data to display (parts of a user data feed for example, data for a live graph, etc), you want to quickly store some data to be processed later (analytics, event processing), or you just use it as an object cache for expensive queries. I have found that with a decently tuned DB, and I mean barely tuned, a decent data model, and sane fetching strategies (don't eager load everything always), a database can be good enough for quite a while, if not forever. The answer I pretty much always get from people when I ask, "Why did you start with (whatever NoSQL they chose)?" Is, "Because (whoever) said it's faster and scales better." I think younger devs that have options besides relational DBs find the barriers to entry on NoSQL lower (it looks mostly like how it went in and it's just JSON) so they pass over the learning SQL and basic data normalization fundamentals.
- jt2190 10y agoI agree with you, but would like to add that by using SQL to query a relational database it is very, very easy to denormalize the data that is copied into an application, so while a relational schema is essentially "static", query results are very dynamic, and can easily strip away all of the "goodness" that results from a normalized data structure. In other words, the application probably needs to deal with normalization and schema if it's taking copies of data, and using a relational database doesn't remove this requirement. (Of course, small applications often get away without enforcing schema, with the understanding that frequent database write failures due to malformed data are ok.)
- catnaroek 10y ago> find the barriers to entry on NoSQL lower Not having to think rigorously about the logical structure of your data is a huge selling point for many. For programmers, because it means they don't have to think. And for employers, because it means they can make do with programmers who don't want to think.
- DankCorrector 10y agoWhy does this comment feel like an ad?
- chatmasta 10y agoAn ad for what, Parse.com? It doesn't even exist anymore, lol.
- pmelendez 10y ago>"Like the author, I really cannot wrap my head around this. While I understand that duplicating documents across collections may make querying faster, what about when you want to change the document? " That's a trade-off you have to analyze to see if it is worth it. If that data is not maintained often then is OK to have duplicated data. One thing you have to realize is that there is no a silver bullet in the CAP theorem and you always would give up something. The other thing to realize is that not everybody needs a distributed database and most problems will live happily with an old-fashioned relational database.
- GoldenMonkey 10y agoThere are two paradigms with databases. Normalization for high volume insert/update transaction requirements (faster writes). Denormalization for (faster reads), typically used for data warehouses and reporting. NoSQL is designed to support both of these scenarios well. And with lower overhead requirements by not having to update a central store... It is used in particular for Big Data solutions. Rather than having to be optimized for one or the other as an RDBMS would require.
- SeanDav 10y agoNot being optimized for a particular use case, makes it a poor choice for anything outside toy, or best case, medium sized applications.
- lmm 10y agoThe point is you have the level of control that lets you optimize for your use case. RDBMSes are a jack-of-all-trades solution and have the advantage of needing very little customization, but they can't be as well-optimized for specific use cases.
- dragonwriter 10y ago> Normalization for high volume insert/update transaction requirements (faster writes). Denormalization for (faster reads), typically used for data warehouses and reporting. Normalization also better supports ad hoc inquiries and reporting compared to denormalization; denormalization is an optimization for regularized reporting patterns (responding to a question that a denormalized reporting design didn't anticipate often requires effectively renormalizing the data, which recovers the logical benefits of normalization, at a potentially very high performance cost.) > NoSQL is designed to support both of these scenarios well. Without the integrity benefits of normalization on updates, and without the benefits of normalization for ad hoc queries. OTOH, there are very few uses of normalized RDBMSs where only the write performance benefits are sought -- usually at least integrity is a concern, and sometimes ad hoc querying as well.
- lmm 10y ago> Like the author, I really cannot wrap my head around this. While I understand that duplicating documents across collections may make querying faster, what about when you want to change the document? You need to propagate the change across every duplicate of the document in every collection where it exists. This means that any "de-duplication" logic needs to happen at the application level, rather than the database level. You're assuming that you need to change things. Almost all big-data approaches work better when you abandon that and go for a log-structured model where you only ever append. > I think the real issue is that "if you have a hammer, everything looks like a nail." Mongo and other NoSQL stores have some real use cases, but people who are more familiar with Mongo than RDBMS are too trigger happy to employ it as a solution to problems where a RDBMS is the clear solution. True enough, but I think the converse is more true. If you're using a traditional RDBMS you're accepting a big series of constraints in exchange for functionality you often don't use. Indices are updated synchronously on every insert, slowing your writes, for the sake of transactional guarantees that most applications aren't written to take advantage of. Queries have to be passed over the wire as strings, so your application will spend a significant chunk of its time building them (hopefully using a library without vulnerabilities to send them over the wire so the database can spend a significant chunk of its time parsing them), or else you get to deal with the db-specific and per-connection quirks of prepared statements, for the sake of supporting ad-hoc querying in a language that frankly isn't great for humans. Tables, materialized views and the like occupy this awkward in-between condition where it's not clear whether you're supposed to create and modify them ad-hoc (and manipulate them programatically) or not; if you really do need to do ad-hoc reporting then they're what you want, but often the performance implications of that make it unacceptable to do in production. I don't think a lot of cases are good fits for RDBMS. If you don't need ad-hoc reporting then you're better off building a data processing pipeline where you produce your results directly rather than the sort of semi-aggregating you end up with with an RDBMS. If you don't need full ACID then it's not worth paying the performance cost of it. And I don't know why there aren't RDBMSes without better query languages and schema definition languages, but there aren't.
- gaius 10y agoIndices are updated synchronously on every insert, slowing your writes, for the sake of transactional guarantees that most applications aren't written to take advantage of. Queries have to be passed over the wire as strings Those are implementation details, not inherent properties of the relational model.
- fragola 10y agoIt's a trade-off. There are a lot of reasons to duplicate (faster reads, etc.), with the big drawback being consistency. The duplications are basically caches, with similar pros and cons. I think a good solution is to use something like DynamoDB and use hot/cold tables. Like, if you're storing something high read/write (i.e. today's olympic stats), keep it in the hot table with a frequent cache timeout. Later, when they become yesterday's stats, move them to the cold table which will basically be read-only, with a longer valid cache. The problem with MongoDB is that it discourages this type of data management, at least how I've seen it used.
- rogerdpack 10y agoIf it's like Cassandra, then you can kind of get around consistency problems by, for instance if you have a "replication" factor of 2, by instructing it to write to "2 instances before returning" (and then you turn around and read it after that, if you want to double check). Though of course while it's writing to the first, then second replica, it'll be inconsistent, but not for too long :)
- mateo411 10y agoYes, that is the difference between strongly consistent and eventually consistent.
- chaostheory 10y ago> "if you have a hammer, everything looks like a nail." I don't like MongoDB either, but normalization is not the answer for every project. For some projects, reading efficiency is so important that people are willing to pay for of it in disk space, an increase in writing costs, and even app complexity.
- icc97 10y agoNormalisation is a good starting point though. If you hit the limits of normalisation then you can always denormalise.
- chaostheory 10y agoIt depends on the problem and current circumstances. Just because MongoDB doesn't work, it doesn't mean alternatives to relational DBs should be ignored.
- clifanatic 10y ago> "forget about normalization! Duplication is okay." Not just duplication. I did some work on a platform that used ElasticSearch as a relatively relational "document" store (that is, a purpose to which a true RDBMS would have been better suited). Besides data duplication, we ran into the problem of data consistency: using the author's TV show example, we would end up with some actor's whose birthdates would be recorded, but some whose age was recorded instead. Some TV shows would list the actors by role, some would just list the actors and the roles in two different lists. Sometimes the TV show start and end dates would be milliseconds since the epoch, some would be dates in YYYY/MM/DD formatted strings. Some would be dates in DD-MM-YY formatted strings. All scattered randomly throughout the data store. Elastic schema means random schema.
- drivingmenuts 10y agoOn the surface, that sounds more like a problem of data cleanup before insertion. Were you doing any validation/normaliztion of the data beforehand?
- clifanatic 10y ago> Were you doing any validation/normaliztion of the data Of course we (they) were - lots of it! Just inconsistently...
- mamcx 10y agoAlso this weird adversion against using the power of a RDBMS. NO VIEWS! NO STORED PROCEDURES! NO TRIGGERS! YOU SQL MUST BE AGNOSTIC! and stuff like that. However, they pick Mongo or anything like that and yep, they must use anything it provide just by necessity. --- People: Using views is easy. Is probably the most simple thing you can get to leverage what a DB can give (plus indexes) and let model the data in different ways and even help in transition from a data model and another.
- tracker1 10y agoOn the flip side... joins are expensive... I worked on an application, where presenting a single page, representing a single item in the application (classifieds site) required upwards of around 40 joins (iirc 36 or 46, I don't remember)... under load (including search result pages), the app was crushed by its' own weight. I think the bigger issue is that people are afraid of separating data at clear boundaries, so long as the orchestration is in place. Having definitive data in SQL, and having denormalized documents in elasticsearch/mongo/rethinkdb works very well... other instances (huge read and/or write throughput) may work better in a Cassandra (or other bigtable solution) with application-generated lookup tables may be better. It just depends on your need. With better support for JSON/XML nested structures and indexes in sql rdbms, it's becoming less of an issue for a lot of types of data/documents.
- pbreit 10y ago40 joins for a classified ad?
- tracker1 10y ago"Normalized" It comes down to there being a lot of "options" for cars/trucks, and each type was a separate set of mappings (2-joins), etc... it could have been simpler and more flat, but it was excessively normalized to be "flexible". Like I said, it was pretty crushing on the database... there were a lot of other expensive calculations that ran per request... pre-calculating, caching, and a few other things got some longevity, bit it was a tangled mess of a beast for what would have been 3-4 collections for a document database (mongodb, rethinkdb, etc).