Organization of data is very important to retrieve data from the databases. It helps us to use the data later. Creating a data model is an iterative process. You can alter the tables based on the data availability and redundancy in the data. Data modeling is used by the software engineers, data engineers, data scientist and whoever uses the data. The relational and non-relational databases do different kinds of data modeling.
The most common relational databases(RDs) are PostgreSQL, Oracle, Teradata, MySQL. In relational databases, data is stored as rows and columns with a unique key that identifies a record. The database or schema is a collection of tables. A table is a group of rows sharing the same labelled elements. If you have a small data volume, you can use relational databases. Additionally, if the business requirement is changed, you can easily alter the data model based on your requirement. Data integrity is very important when you do a transaction, especially in a bank. If you require ACID transactions your business, it is supported by the relational database.
Atomicity: The whole transaction is processed or nothing is processed. For example, if money is transferred by you from your account to your friend's account, if the money detects from your account but not credited in your friend's account, the transaction is not complete. This transaction should be rolled back to the old state before you initiate the transfer.
Consistency: Only the transactions abide by the constraints and rules are written to the database otherwise the database keeps the previous state. For example, all the account number hat you put in the account columns must have the same format.
Isolation: Transactions are processed independently and securely, order does not matter. If a data in a table is altered, that table is not available to the other user until you have done the changes.
Durability: Completed transactions are saved to the database even in cases of system failure. A commonly cited example includes tracking flight seat bookings. So once the flight booking records a confirmed seat booking, the seat remains booked even if a system failure occurs.
The relational database is not of use in the below scenarios:
- store a large amount of data and various kinds of data
- high availability-RDs can be a single point of failure as they are not distributed. Thye can be scaled vertically
- fast reads-in RDS, the queries take time to retrieve data
NoSQL databases are Not Only SQL databases. They are non-relational databases. They are used in case you have a huge amount of data if the database has to be available always and to do a faster read. The NoSQL databases are scalable very quickly. Horizontal scalability is the ability to increase the space and performance of the database by increasing the nodes. They can store data in different formats like structured, semi-structured, unstructured, JSON, and XML documents are easily handled by the NoSQL databases. To sum up, they are used when the situation is not handled by the relational databases.
You cannot use NoSQL databases in the below cases:
- If you need a consistent database with ACID transactions, you cannot use NoSQL databases. (Although NoSQL is consistent, they are cannot support ACID transactions)
- Need to do joins, aggregations and analytics (you have do join tables across thousands of servers because of the distributed nature)
- Small data sets and changing business requirements
Note: NoSQL DBs like MongoDB, MarkLogic does support some level of ACID transactions.