12 July, 2016

NoSQL vs SQL (short version)
By: Matthew Jackson

They both have their advantages. I will list 4 major advantages of each, there may be more but this is the short version.

SQL

  • Relationships. You can easily have two tables linked together with a shared id column. Getting data is easy and changing data means just changing the data in one spot.
  • Maturity. SQL has been used professionally for quite some time and many bugs have been found and fixed. Security holes have been identified and taught how to be beaten. Many times incorporated into frameworks nowadays.
  • Data Integrity. Thisis simply to make sure data is of a specific type. This way you can easily know in advance your getting a string representation or a date for example. You don't need to check (assuming you avoid security issues on input). 
  • Relationship Integrity. This means making sure if a row is deleted any relationships that depend on that are taken care of and it points to something. Either both are deleted or neither.

NoSQL

  • Free flowing. This can be great, as you can easily evolve your data. There are no restrictions on type of data. In one entry the supplier might be a name, in another it could be a number. This can be great because as you develop your database you can change it and the database won't complain, but your code might, so be careful. Or perhaps certain suppliers have a special code and some just a name. You can more easily tailor your database. Some elements have more data like location and some not.
  • Nested. Data can easily be nested. This makes logical sense some times. For example a family tree. Much easier to logically represent in NoSQL than in SQL. Like the earlier point, it can easily grow.
  • Speed. Many times NoSQL is faster since all the data is contained in one lookup, and nested data helps here. Sadly if you have relationships between different collections, don't expect NoSQL to be faster, it will be the same or slower. At the end of the day this likely depends on how you design your database.
  • Clustering. NoSQL has been written from the ground up these days to more easily allow clustering over multiple servers. You still might need some help eventually getting all of that setup.

Conclusion

SQL is the right choice in my opinion when you have lots of relationships in your data. Simply because NoSQL will require you nowadays to either replicate the data or use an id to do an additional lookup. For example, a list of friends. If you store their names, when they change their account name then you need to update all friends who had stored their name. If you store ids to their account, then each time you want their name you need an additional lookup.

If your not having too many joins and relationships, the next best decision might be what your framework uses primarily. You will have a better community to support you when you have questions. Or if there are plugins/libraries they will already likely be written with your database in mind (though hopefully database choice shouldn't affect the plugin working as all of this is hidden).

Will you be storing files? You will have more ease storing them in NoSQL than in SQL. Whether or not this is a good idea is a whole new conversation.

Finally, if you want your demo up quick and might need to evolve it rapidly. Perhaps NoSQL is a good choice. A demo doesn't have to be correct and designed well. It is a demo. If it is the basis for your future work and you will build on top of it, you should be designing it well from the beginning.

 

A word of note is that many databases will borrow concepts from each other making SQL and NoSQL harder to decide. Just like Java is allowing functional programming now, many great ideas are taken from others and incorporated. If your not sure, go with what you know; unless you enjoy the adventure and new skillset.

Tags: SQL, NoSQL