With the recent prevelance of NoSQL databases why would I use a SQL database?

Question

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.

Answer 1

NO-SQl Means - Non Relational Database!

NoSQL databases are better for large applications where scalability is important.

Nosql Database are Insertion and fetching both are fast

NoSQL databases are Fetching record is 10X faster in compare to sql database

calable and flexible datastore : This is the primary reason for moving away from relational database.

It can work on Structured and Unstructured Data. It uses Collections instead of Tables

Answer 2

There are variants of Not only SQL databases outhere each has its own pros and cons.

there are document or object based, column based (wide row), key value based and graph based, and thats only what i can think of right now. Each of those types of database has its weaknesses and its strongnesses (compared to others and to RDBMS).

The real question that you need to ask yourself when making a decision for which DB type to choose is how you are going to use the data?

in most common cases, at least untill some level of object complexity, and for non-huge data, RDBMS care less about how the data is used and more about the data itself. In RDBMS you just need to know your data structure and internal relationships and after you realize that, you just put it in a normal form schema and if you put the right keys and indexes you get kicking performance on most queries.

In a NoSQL database its more crucial, for example a specicific weakness of the document based DBs is that if you need to make complex queries regarding multiple documents in most cases you will not get better performance than you would from a RDBMS.

for example, if you are maintaining Order documents, and wish to query the order with the maximal profit that was taken in a range of dates, afaik if you are not an expert (as i am no such) you will end up having a O(n) query, while in RDBMS it will take less and most certainly be more performant even if you are a MongoDB expert.

In conclusion, if you know in advance how your data is going to be used, and you know a document db would be performant for your use case then yeah, take that document DB, but if you are not sure how your data will be used, than RDBMS would generally be wiser decision.

And of-course there is the BigData argumant you need to take in account, as RDBMSs dont scale out (cant easily add nodes to support more traffic), and gets less performant when it deals with HUGE data (may start to lag in GBs or PBs).

Also, keep in mind that RDBMSs are way older and had been developed extensively over the years than document DBs which makes RDBMS contain more optimizations and tools than any of the NoSQL alternatives.

Answer 3

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.

Answer 4

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.

Protip: drop the objects, they're not the right tool for the job.

Answer 5

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.

Answer 6

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?

Answer 7

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.

Answer 8

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.

Answer 9

Relational data modeling is a formal, mathematical solution for representing complex data without redundancy and without allowing anomalies. You can design an optimal database design from the data relationships themselves. This is the process of relational database normalization.

Non-relational data modeling has no formal way to define the best database structure from the data. You can design a database based on your anticipated usage; that is, your queries determine the best data organization, not the data itself.

In non-relational databases, you can never be sure that data conforms a certain document structure. You could have documents left over in the database from an earlier revision. So your application code had better be able to "discover" the structure of each document, perform conversions if necessary, and hope that references between data collections are satisfied.

In relational databases, you can depend on data integrity being an integral part of the model. If you design for normalization and you set up constraints properly, you know you'll never have orphans or data anomalies.

Non-relational databases give you one type of efficiency, as you're designing the database. Relational databases give you another type of efficiency, as you're using the database.

That said, the specific type of problem you've been working with -- object graphs -- is tricky to accomplish efficiently with plain SQL. But I think you'll find it's not much easier with NoSQL databases.

---

Re your comment: Granted, consistency is not a priority for every app. That doesn't make the value of consistency "insubstantial" for the apps where it is important.

You asked about why you would use relational databases -- you'd use them when the benefits of relational databases fit the priorities of your project.

Don't drive a nail with a screwdriver, and don't turn a screw with a hammer. There's an appropriate tool to solve each type of problem.

Answer 10

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.