Relational vs Non-Relational

Concept

For 30 years, Relational (SQL) databases completely dominated the software industry. They force data into rigid, pre-defined tables (Schema-on-Write) and guarantee absolute data integrity (ACID properties).

In the late 2000s, as companies like Facebook and Google began handling Petabytes of unstructured user data, standard SQL databases physically couldn’t scale. This birthed the NoSQL (Not Only SQL) movement.

NoSQL databases abandoned strict tables and ACID guarantees in exchange for flexible, schemaless data storage and massive horizontal scalability.

The Core Differences

1. Schema

• SQL (Strict): You must define the table structure before you can insert data. If you want to add a nickname field to the users table, you must run a blocking ALTER TABLE command across 10 million rows.
• NoSQL (Schemaless): You can insert whatever you want, whenever you want. You can insert a user with a nickname, and the very next user without one. The database doesn’t care.

2. Scaling Architecture

• SQL (Vertical): Designed in the 1980s when servers were giant mainframes. They are designed to scale by buying a bigger, more expensive server (Scaling Up). Distributing a relational database across 10 servers (Sharding) is incredibly difficult because it breaks JOINs and Foreign Keys.
• NoSQL (Horizontal): Designed from day one to be distributed. They are built to run on clusters of hundreds of cheap commodity servers (Scaling Out). Adding a new server to a Cassandra or MongoDB cluster is often just a single command.

3. Data Integrity vs Speed (ACID vs BASE)

• SQL (ACID): Prioritizes absolute correctness. If a bank transfer fails halfway through, the database violently rolls it back. (Atomic, Consistent, Isolated, Durable).
• NoSQL (BASE): Prioritizes speed and availability. It uses Eventual Consistency. If you post a photo, it might take 2 seconds for your friend in another country to see it. (Basically Available, Soft state, Eventual consistency).

The Modern Convergence

In the 2010s, developers fought holy wars over which was better. Today, the war is over, and the answer is: They both stole each other’s best features.

• PostgreSQL (SQL) added JSONB, giving developers the exact same schemaless flexibility as MongoDB.
• MongoDB (NoSQL) added multi-document ACID transactions, giving developers the exact same financial safety guarantees as PostgreSQL.

Today, you almost always start with SQL (PostgreSQL) because it handles 95% of all use cases perfectly. You only reach for a specialized NoSQL database when you have a very specific, massive-scale architectural requirement.

Interview Questions

Q: A startup is building a standard E-Commerce store (Users, Products, Carts, Orders). The CTO insists on using MongoDB “because it’s Web Scale and faster than SQL.” Is the CTO right?
A: No. An e-commerce store is deeply, fundamentally relational. An Order must belong to a User. A Cart must contain Products. If you use MongoDB, you cannot easily enforce Foreign Key constraints. The Node.js application must manually ensure that a user isn’t deleted while they have an active order. This introduces massive, dangerous complexity to the application layer for absolutely zero benefit, as a standard PostgreSQL database can easily handle the scale of a typical e-commerce startup.

Q: Explain the CAP Theorem.
A: The CAP Theorem states that a distributed data store can only guarantee two out of the following three properties simultaneously:

1. Consistency: Every read receives the most recent write.
2. Availability: Every request receives a non-error response.
3. Partition Tolerance: The system continues to operate despite network cables being cut between servers.
   Because network failures (Partitions) are physically inevitable, you must choose between Consistency and Availability.

• SQL databases (like clustered PostgreSQL) generally choose CP (Consistency). If the network breaks, the database shuts down to prevent data corruption.
• NoSQL databases (like Cassandra) generally choose AP (Availability). If the network breaks, both sides of the cluster keep accepting writes, and they just resolve the conflicts later (Eventual Consistency).