SQL vs. NoSQL vs. CQL: Comparisons & Tested Scenarios
— data analysis on Relation DB vs Document DB vs Graph DB!!
Tech Stack — Oracle, MongoDB, Neo4j
In current software architectures with N-tier environments, the principles like single responsibility or separation of concerns make platforms stacked with multiple technologies where engineers are not just inclined with any particular technology, instead, they have to understand or be ready to work with different technologies out of their expertise.
One such situation is where on a day to day basis engineers have to interact/process a variety of data structures like (tables, JSON, node structures) on a variety of data models(rational, document, graph) using a variety of API’s(SQL, NoSQL, CQL). And it’s difficult to expertise on all varieties. Today, In this post I would like to detail these structures to make them handy for the developers on their day-to-day job!
Glossary:
- Conceptual Comparisons
- Data Model’s
- Query analysis
Conceptual comparisons: Initially we will look into conceptual differences between these APIs to understand the nature of the data and data model we interact with!
Data Models: Using the below example objects, we can design models on different databases and display differences in b/w, we will also use these data models for our query analysis as well.
Example: Products — is operational data of product which contains category and supplier details as well, Categories — describe the product and its category, Suppliers — company’s which supplies these products and addresses
Relational Model: In this model, the objects are referred to as entities, and relationships are created based on key constraints
Document-based model: In this model, the documents are a collection of JSON objects, and a relationship is created using references inside a JSON object and then normalized based on those references.
Note: In the NoSQL model, the document is normalized based on references inside the main document (here Products).
Graph Model: In this, objects are called nodes, and relationships among nodes are called edges. Every node will have properties that categories a similar node into a different node.
Query Comparisons: We will now write queries as per the type of models we designed. here I will try to describe queries using our skeleton query on SQL, which is also a default query language for most developers and easy to relate to. skeleton:-
SELECT columns, columns
FROM table1
WHERE condition/expression
JOIN table2
GROUP BY condition
ORDER BY
LIMIT
I tried to display these comparisons to help understand and to make use of basic query structures in technologies used in modern projects. Thank you for reading this post!!
References:
