Are you having lots of queries flooding your mind when you think about SQL and NoSQL? Then this article gives you a complete run down on the major differences between the two, and their benefits.
- What are the Key differences between SQL and NoSQL?
| SQL | NoSQL |
| SQL Databases are called Relational databases or RDMS – Relational Database Management Systems. | NoSQL Databases (DBs) are called Non-Relational or Distributed Databases. |
| Data Storage is based on a single data model – relational model. This model is table based i.e. Consisting of rows and columns of data. | Data Storage is not based on a single data model. Most outstanding ones are key-value pair, graph, document, and columnar. |
| SQL DBs use only SQL as the language for querying. The syntax of SQL is standard across any database. | NoSQL DBs use UnQL (Unstructured Query Language) for querying. The syntax of using UnQL varies from database to database. |
| Data stored as per fixed schemas. Each row must have data specific to a column. | Schemas are dynamic. Each row need not have data for each column. |
| Database scaling is possible with vertical scaling i.e. more data storage requires a bigger server. Though multiple RDBMs servers can be added, this is complex and time consuming. | Database scaling is possible with horizontal scaling across multiple servers. |
| SQL Databases follow ACID ((Atomicity, Consistency, Isolation, Durability) properties to ensure that database transactions are reliable. | NoSQL database follows the Brewers CAP theorem (Consistency, Availability and Partition tolerance). |
| SQL Databases get classified as either open source or non-open source. | NoSQL DBs get classified based on the data storage type- Graph DBs, Key-Value store DBs, and Document store DBs. |
2. SQL Vs NoSQL – Which to Choose?
SQL Vs NoSQL does not mean that you can stop using SQL. Opting for NoSQL does not imply that it is better than SQL. The choice between SQL and NoSQL depends broadly on the following:
- Type of data that you need to store.
- If data type is changing rapidly or not.
- Growth factor of data size.
- Volume of data.
| Choose SQL | Choose NoSQL |
| If your data is within the limits of relational database. | If your data is fringing on the limitations of relational database or NoSQL databases are highly preferred for large data sets (i.e. for big data). |
| If your data operations demand high transactional rates or complex data queries needing control over execution plans. | If the scale of your data operations is vast and needs scaling. |
| If your data is structured and can be stored with pre-defined schemas. | If your data is unstructured or is a combination of both structured & unstructured. |
| If your goal is to support isolated indivisible transactions whose changes are persisted and leave the data in a consistent state. | If your goal is to support only either isolated indivisible transactions whose changes are persisted or leave the data in a consistent state. |
| If changes to data are not too many. | If changes in data are inevitable. |
3. What are the strengths and weak points of SQL?
| Strengths | Weak Points |
| SQL is powerful for supporting database CRUD (Create, Read, Update, and Delete) operations and is an industry-standard. | Since the data is stored in relational tables, there is a performance bottleneck of operations that span a lot of tables. |
| SQL databases employ pre-defined optimizations like column index definitions to help speed up query operations | Though SQL databases can scale up vertically insanely high, eventually there will be an upper limit and vertical scalability is expensive. |
| SQL databases are not best fit for hierarchical data storage. |
4. What are the Strengths & Weak points of NonSQL?
| Strengths | Weak-Points |
| NoSQL database fits better for the hierarchical data storage as it follows the key-value pair way of storing data similar to JSON data. | NoSQL don’t have standard interfaces to perform complex queries, and the queries themselves in NoSQL are not as powerful as SQL query language. |
| NoSQL DBs have simpler access patterns. | NoSQL for transactions purpose, it is still not comparable and stable enough in high load and for complex transactional applications |
SQL Database Examples
MySQL Community Edition
MySQL database is very popular open-source database. It is generally been stacked with Apache and PHP, although it can be also stacked with nginx and server side Java scripting using Node.js.
The following are some of the benefits and strengths of MySQL:
- Replication: By replicating MySQL database across multiple nodes the work load can be reduced heavily increasing the scalability and availability of business application
- Sharding: MySQL sharding is useful when there is large number of write operations in a high traffic website. By sharding MySQL servers, the application is partitioned into multiple servers dividing the database into small chunks. As low cost servers can be deployed for this purpose, this is cost effective.
- Memcached as a NoSQL API to MySQL: Memcached can be used to increase the performance of the data retrieval operations giving an advantage of NoSQL API to MySQL server.
- Maturity: This database has been around for a long time and tremendous community input and testing has gone into this database making it very stable.
- Wide range of Platforms and Languages: MySQL is available for all major platforms like Linux, Windows, Mac, BSD and Solaris. It also has connectors to languages like Node.js, Ruby, C#, C++, C, Java, Perl, PHP and Python.
- Cost effectiveness: It is open source and free.
MS-SQL Server Express Edition
It is a powerful and user friendly database which has good stability, reliability and scalability with support from Microsoft.
The following are some of the benefits and strengths of MS-SQL:
- Integrated Development Environment: Microsoft Visual Studio, SQL Server Management Studio and Visual Developer tools provide a very helpful way for development and increase the developer’s productivity.
- Disaster Recovery: It has good disaster recovery mechanism including database mirroring, fail over clustering and RAID partitioning.
- Cloud back-up: Microsoft also provides cloud storage when you perform a cloud-backup of your database
Oracle Express Edition
It is a limited edition of Oracle Enterprise Edition server with certain limitations. This database is free for development and deployment. The following are some of Oracle benefits and strengths:
- Easy to Upgrade: Can be easily upgraded to newer version, or to an enterprise edition.
- Wide platform support: It supports a wide range of platforms including Linux and Windows
- Scalability: Although the scalability of this database is not cost effective as MySQL server, but the solution is very reliable, secure, easily manageable and productive.
5. Name some NoSQL DBs:
| Database Name | Features | Benefits & Strengths |
| MongoDB |
|
|
| Couch DB |
|
|
6. Comparison between Relational and Document based Data Models:
Document Databases
This section is an attempt to explain a Document Oriented Database with comparisons to a Relational Database. The image above sums up the main difference between relational and document based data models. Relational databases have rigid data format (table form) whereas the document based data model does not have a predefined data format. A document may store an address, a story, or any kind of information which is not pertaining to any particular format. A document is a set of key-value pairs. Documents have dynamic schema. Dynamic schema means that documents in the same collection do not need to have the same set of fields or structure, and common fields in a collection’s documents may hold different types of data.
Below given table shows the relationship of RDBMS terminology with Document based Database:
| RDBMS | Document Based DBs |
| Database | Database |
| Table | Collection |
| Tupe/Row | Document |
| Column | Field |
| Table Join | Embedded Documents |
| Primary Key | Primary Key |
Data Modeling in Document based DB:
Taking the example of blog site, to design a schema for it, the following are to be considered:
- A blog post has the unique title, description and url.
- A blog post can have one or more tags.
- It has the name of its publisher and total number of likes.
- A Post has comments given by users along with their name, message, data-time and likes.
- On each post there can be zero or more comments.
In RDBMS schema design for the above requirements; a minimum of three tables are required:
In a document based schema, only one collection – post is required and is as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
{_id: POST_ID Title: TITLE_OF_POST, Description: POST_DESCRIPTION, By: POST_BY, url: URL_OF_POST, tags: [TAG1, TAG2, TAG3], likes: TOTAL_LIKES, comments:[] { User: ‘’COMMENT +BY’:, Message: TEXT, dataCreated: DATE_TIME, like: LIKES }, { User: ‘’COMMENT +BY’:, Message: TEXT, dataCreated: DATE_TIME, like: LIKES } } |
Leave a Reply