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 possibilities:
1. The server 2 will return the last updated value, but not the latest value. Here the data is not consistent. We satisfied Available and Partition tolerant.
2. But if we want to return the latest value, therefore being consistent, but we need to wait for the server to be updated. Till that time the server will be unavailable. We satisfied Consistency 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 Available and Consistency.
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 shows might be different from the actual website. Here available takes presidency over consistency.
Below are the tools that supports the following:
Consistency and Available:
Consistency and Partition Tolerance
Available and Partition Tolerance