After developing software for about 5 years now, I have spent probably atleast 20% and perhaps up to 40% of that time simply making a RDBMS able to save and retrieve complex object graphs. Many times this resulted in less than optimal coding solutions in order to make something easier to do from the database side. This eventually ended after a very significant amount of time spent in learning NHibernate and the session management patterns that are part of it. With NHibernate I was able to finally eschew the large majority of 100% wasted time of writing CRUD for the 1000th time and use forward generation of my database from my domain model.

Yet all of this work still results in a flawed model where my database is merely the best attempt by SQL to imitate my actual object. With document databases this is no longer the case as the object becomes the document itself instead of merely emulating the object through tables and columns.

At this point I'm really starting to question why would I ever need SQL again?

What can really be done substantially better with SQL than a document database?

I know this is somewhat of leading into a apples to oranges comparison especially when you factor in the various types of NoSQL databases having widely different feature-sets but for the sake of this argument base it on the notion of NoSQL databases can inherently query objects correctly and not on the limitations of a key value store. Also leave out the reporting aspect as that should generally be handled in a OLAP database unless your answer includes a specific reason you would not use a OLAP database for it.

You can't really ignore the reporting aspect of RDBMS'. Agreed that reporting isn't needed for most cases, but where it is needed, those joins can be quite handy.
–
AnuragJul 20 '10 at 22:11

2

Hmmm, finger on the button when it becomes to argumentative again.
–
WrikkenJul 20 '10 at 22:13

1

SQL is usually far superior in finding relations between data, statistical analysis and in safe transactions, and has a lot less duplication of data. Some reading: cattell.net/datastores/index.html SQL and NoSQL both have their uses & places, anyone trying to use one tool for all problems either has a very limited scope of problems, or a hard time hammering a screw in.
–
WrikkenJul 20 '10 at 22:15

3

-1 Doesn't really seem to ask a question. Title makes an unsupported provocative claim ("NoSQL is prevalent" which from the SO perspective it isn't) Selects own zero vote answer.
–
Conrad FrixAug 10 '10 at 18:40

2

@Chris. No its not a question, its a declaration about your state of mind. The question mark is probably incorrect unless you're asking if you are, or are not "starting to question sql". Later you do ask subjective questions using statements like "outshine" and "substantially". IMHO it seems like you want the SO community to validate what you already believe. It seems like a blog would be a better venue for your thoughts, like this. blogs.computerworld.com/15510/…
–
Conrad FrixAug 11 '10 at 15:48

7 Answers
7

At Amazon I worked with a lot of code. Most of the code I worked one was code nobody really understood anymore. It was riddled with special case handling that wasn't well understood because it was an accretion of quick patches over a long period of time. If you wanted to fully understand the effect of a change you were making you were out-of-luck. In essence, you were forced to add to the accretion.

I also worked with a lot of data. The structure of the tables in SQL made excellent long-term documentation for the data. The database was relatively easy to work with directly, and the structure of the data made sense. There were people who's job it was to manage the structure and integrity of the data.

I fear that a NoSQL database, with its lack of well-documented structure, would slowly acquire all the evil qualities of the code I worked on. It would end up filled with data from old structures that nobody really understood anymore, and become a vast patchwork of mostly useless garbage.

I see the main benefits of SQL databases as the forced documentation that maintaining the database structure and consistency rules requires. Those benefits do not have an easy short-term measure like speed of a query or transactional consistency. They are long-term benefits that affect the usefulness of your data over an extended period of time.

As a second, related point, I find it more useful, when using ORMs and the like, to map out my data and then decide how that will translate into objects in the application I'm writing. The data and its relationships represent a long-term archival structure that may be used for a variety of purposes.

The structure of the object relationships in the application are there for the purposes of that application. A given set of data represented in SQL tables and relationship constraints will have many possible object models that represent it in an application, and each of those object models will reflect the goals of that particular application. But the data and its structure exist independently of any given ephemeral use that might be made of them.

I see the arguments people make about 'reporting' as being arguments that different applications can usefully view the same set of data in very different ways.

Personally, I think SQL is a good model to use directly for archival data, infrequently modified data, or data with extremely high consistency requirements. And I think that I will continue to use relational algebra to define the overall structure of my data even if I'm storing it in a NoSQL database. And I will not change the structure of the data in the NoSQL database without first modifying the relational structure describing it. This will allow me to map my NoSQL databases back to SQL so I can still use SQL for long-term storage and warehousing and force me to maintain the data structures in a well documented form.

Doing things this way will also assist me when I have to pull data out of the NoSQL database for use in applications that were not envisioned when the database was created.

Of course, there is some data who's structure naturally fits NoSQL and where generating a relational schema for it would be pointless. For example, storage of actual documents, storage of pictures or other media, or other large blobs of data that has no structure that might be useful to represent. This distinction is very tricky though. Pictures and movies do have structure to them, just not generally structure you need to store in a database. A blog post may have structure as well if you have a system designed to try to read and understand it, and that may well be structure you want to maintain a record of.

"And I will not change the structure of the data in the NoSQL database without first modifying the relational structure describing it. This will allow me to map my NoSQL databases back to SQL so I can still use SQL for long-term storage and warehousing." This seems like a nearly inordinate amount of work, wouldn't this effort better spent just building and maintaining a proper import process for the datastore -> datawarehouse instead?
–
Chris MarisicAug 6 '10 at 14:25

3

@Chris Marisic: And what happens when the importer program becomes something that someone needs to spend a few weeks to understand? No, IMHO, it's vitally important that you always have a really good handle on exactly what data you have in your database, what it means, and how it relates to the other data. Keeping an SQL schema (or any schema really) of it outside the database is a means for achieving that.
–
OmnifariousAug 6 '10 at 15:00

I awarded you the bounty because using a database schema as a rigid model of your domain while might not be something I'd do definitely offers a case where Sql is substantially better suited for.
–
Chris MarisicAug 10 '10 at 12:53

it depends on what you are trying to do. when you need to do searching on different fields of your objects then SQL is good. if you don't need to do searching and you have very complex polymorphic tree like structures then SQL is horrible.

i've worked on app that allowed users to build web pages by joining little fragments together and the original serialization used key/value SQL tables. all the fragments had properties which were stored (fragment, property, value). so schemaless but still a lot of heavy lifting. probably the worst of both worlds because you don't really get much data validation from the database, it is very difficult to look at the tables and understand what is going on and there is still a lot of work to write it to the db and read it back.

we've also done a similar app but we learnt our lesson and we just take plain java classes and encode them using JSON. the user just edits their page in the front in a rich ui. clicks save and the whole page is sent back to the server as a json object. the server then does validation on the object to make sure all the constraints are correct which should always be true unless a user has been tampering or there is a bug in the code. then the object is written to a row by encoding to back to json.

this works well for us because we never want to deal with part of the object. we always deal with the whole of the object so JSON is not only easier but it is faster than doing the 40+ queries on each read we would have to do if it was properly normalized.

Tooling is much better for SQL. NoSql has a buggy reputation. But even assuming those two differences even out...

I have the opposite experience from you in modeling complex objects in SQL. To say that tables and columns are at best an 'emulation' of your objects, that's a bit semantic. Any serialization of your objects would also be an emulation: While a document database or xml or whatever may feel like a better emulation than tables/columns, it tends to be less powerful technology. ORMs have helped immensely to bridge the gap from RBDMS to object oriented languages.

Since relational theory was formalized, SQL has been king. Hierarchical dbs (which document databases are) lost, relational dbs won. I would ask yourself, given that history, is your problem all that different from the majority of problems over the last 30 years that you need to revert to hierarchical form?

NoSql dbs are hip now for problems that require horizontal scaling (which SQL doesn't do well now). Does your problem require that?

I would disagree that serialization is an emulation of objects as it is in truest form a physical representation of the objects. Whereas using tables/columns is an emulation of storing the data in a way similar to serialization but is not serialization. In regards to the question about the last 30 years about SQL being king is in no way a validation of it being objectively better than other databases. In 30 years programming has become fundamentally object orientated which even in its early days was the reason hierarchical databases were created.
–
Chris MarisicAug 10 '10 at 13:12

Prior to current times non relational databases just generally weren't successful. This could be from any number of a reasons whether the lack of need for horizontal scaling obscured the major flaws in SQL databases, poor marketing of the need of other tools, lack of adoption from business, etc any and all of these and more contributed to previously relational databases being accepted as the only real solution but times change and tools evolve and from everything I've seen this year nonrelational databases will have significant growth in usage over SQL because SQL sucks for developers.
–
Chris MarisicAug 10 '10 at 13:15

When I've investigated noSQL-style databases, I found that they did not provide ACID, nor did they provide relational features(not being relational databases). Since I like data consistency, and I have usually wanted some sort of relational feature, I've not selected noSQL databases.

However, I don't use the ORM tools out there, I tend to write SQL itself.

My key question was where would a SQL database really outshine a document database and from all the responses there really doesn't seem to be much.

Given that NoSQL databases come in just as many variations of types of databases as relational that both match all or some parts of ACID depending on which database you use that at this point they are basically the equitable for solving problems.

After this the key differences would be tooling and maturity which SQL databases have a much larger grasp in for being the established player but this is how it is for all new technology.

Don't forget you took away two important arguments: Query flexibility/Reporting and the lack of power of key/value store. Basically, you can boil your question down to, is Sql without all that's good about it better than NoSql without all of its problems. Also from a business perspective (leaving aside technology), an important factor is community adoption, and any SQL implementation is still light years ahead of NoSql in that regard.
–
ShlomoAug 10 '10 at 14:42

A key-value store is a very special tool used for specific purposes and has never been meant to replace a SQL database. I did not state any where about query flexibility, I stated about reporting which should in theory be done in an OLAP database, not a relational database.
–
Chris MarisicAug 10 '10 at 17:01

@coolgeek that article ever further resounds my point of not using a normal database for reporting, FTA "While ROLAP uses a relational database source, generally the database must be carefully designed for ROLAP use. A database which was designed for OLTP will not function well as a ROLAP database. Therefore, ROLAP still involves creating an additional copy of the data. However, since it is a database, a variety of technologies can be used to populate the database." So while it might leverage a direct relational database for the storage it still requires you to maintain an OLAP database.
–
Chris MarisicAug 13 '10 at 15:06

I look back at this answer, even written 3 years ago I see without a doubt it is definitively correct.
–
Chris MarisicOct 28 '13 at 17:09

It is important to remember that relational is still (and will continue to be for some time) the platform of choice for: transaction processing, master data management, reference data, data warehousing (in MPP), BI (though inverted column database are outstanding at query performance). Given the current state of NOSQL, it is nearly absurd that it can replace relational for the above uses.

I specifically separated the role of OLTP vs OLAP, alot of your list directly falls into OLAP. I vehemently disagree with your usage of "transaction processing", unless you mean something different than OLTP because NoSQL is built entirely for OLTP.
–
Chris MarisicJan 17 '14 at 14:24

My way of looking at the question is the opposite: Why would I ever need noSQL at all ?

SQL provides me with relational modelling, transactions, triggers, keys, constraints, dynamic schemas that can be modified in the blink of an eye YET guarantee data integrity, blazing fast complex queries on data that is represented in its purest and cleanest form.

Your problem is that you're trying to fit square pegs in round holes: objects and rdbms's don't go well together, because the RDBMS is designed to handle many of your more complex get/set logic, and enforce consistency, which is exactly what you expect from your object layer.