Sql versus NoSQL

This is a set of evolving thoughts and discoveries about NoSQL – when to use it – when not to…

  At a high level , the table shows the NoSQL analagous concepts to familiar SQL terms. 


Relational Model (SQL) NoSQL model ( e.g. Mongo DB )
database database
table collection
row document or BSON document
column field
index index
table joins embedded documents and linking
primary key
Specify any unique column or column combination as primary key.
primary key
In Mongo DB, the primary key is automatically set to the _id field.
aggregation (e.g. group by) aggregation framework
See the SQL to Aggregation Framework Mapping Chart.

Overview – Mainly around why we DESIGNED RELATIONAL databases in the first place

Relational databases helped eliminate data duplication (and errors related to duplication of data – for e.g. – having to update the SAME data in multiple places).  As it happens, NoSQL in fact does not overcome this shortcoming – and in fact, if your app queries consist of lots of updates, NoSQL is not as efficient a solution as relational (and will be as error prone as flat files were prior to relational dbs).

However, if your app queries are primarily READS – then going through a set of RELATIONSHIPS to read a single record of user data – is a lot of overhead. And NoSQL wins hands down – by storing all the relevant data in ONE record.

E.g. – Author Information and Book Information – In SQL you would separate these into different tables – and provide a RELATIONSHIP between the two – so that any updates to Author Info – only needs to happen in a single place – and will automatically be reflected in any BOOK query and AUTHOR query as well.

In No SQL, if the Author’s name changes, then the info will need to be updated in the AuthorInfo and the BookInfo NoSQL records (since they both contain the author name). Multiple places to update data – more error prone (there are a few patterns to work around this – but the basic issue remains).

Foreign Keys

In NoSQL , you can define relationships (it is hard to do anything meaningful with data without defining relationships). Relationships are of two types – GUID based – and Parent Child based. Say – you want to associate UserComments with a User. You would define Comments as a child of User – and it would be a Parent-Child relationship. Now, you could run queries (searches is more appropriate, since QUERY applies more to the relational model) against this parent child COLLECTION of objects.

Indices

Both models rely on indices.

Summary – When to Use Relational , When to Use NoSQL – Tradeoff

The important thing to understand, is how to perform efficient queries and to understand the referential integrity tradeoff that is made when we de-normalize to achieve high performance applications. Always consider the application query patterns when designing the data model.

Specializing in high volume web and cloud application architecture, Anuj Varma’s customer base includes Fortune 100 companies (dell.com, British Petroleum, Schlumberger).
Anuj’s training as a mathematical physicist followed by years of advanced computer programming is unique in the industry.

For Anuj’s popular technology seminars and science and scientific computing seminars, please visit ANUJ.COM

For Anuj’s Mathematical Models and Math Modeling related consulting , please visit anuj.com.

All content on this site is original and owned by AdverSite Web Holdings, Inc. – the parent company of anujvarma.com. No part of it may be reproduced without EXPLICIT consent from the owner of the content.

Anuj Varma – who has written posts on Anuj Varma, Technology Architect.


Leave a Reply

Your email address will not be published. Required fields are marked *