If you are building a distributed database, and you expect that your database should have below 3 properties:
Consistency: The read is the most recent write.
Available: The server should be available all the time
Partition Tolerant: There are multiple database, if any one database goes down, then the data should still be available.
So CAP theorem states that, at a time you can have any of the 2 properties.
So let us test this theorem by using an example:
We have 2 servers:
Server 1 and server 2, if we write to server 1 and read from server 2, there are 3 posssibility:
1. The server 2 will return the last updated value, but not the latest value. Here the data is not consistant. We satisfied Avaibility and Partition tolerant.
2. But if we want to return the latest value, therefore being consistant, but we need to wait for the server to be updated. Till that time the server will be unavailable. We satisfied Consistancy and Partition tolerant.
3. The message gets to the server 2, but that is dependent upon the network. If there is any disturbance in the network, the data will not be the latest one. Hence partition tolerant. We satisfied Avaibility and Consistancy.
Depending upon the use case of your project, you can choose which is a priority and choose upon the correct database.
Simple example is a coupon website. When a user visits the website, he needs that website to be available. Sometimes the prices it shouws might be different from the actual website. here avaibility takes presidance over consistancy.
Below are the tools that supports the following:
Consistency and Availability:
RDBMS
Consistency and Partition Tolerance
MongoDB
HBase
Redis
Availability and Partition Tolerance
CouchDB
Cassandra
DynamoDB