Database Basics: SQL vs NoSQL
A database is an organized collection of data, generally stored and accessed electronically from a computer system. The two main categories of modern databases are Relational (SQL) and Non-Relational (NoSQL).
1. SQL (Relational Databases)
SQL databases are table-based. They represent data in rows and columns, similar to a spreadsheet.
- Structure: Tables, Rows, Columns.
- Schema: Rigid. You must define the table structure (column names and data types) before adding data.
- Scalability: Typically Vertical (adding more power/RAM to the server).
- Examples: MySQL, PostgreSQL, SQLite, Oracle, Microsoft SQL Server.
- Best For: Complex queries, financial transactions (ACID compliance), and data where relationships are key.
Example (SQL Query)
-- Create a table
CREATE TABLE Users (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50)
);
-- Insert data
INSERT INTO Users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');
-- Query data
SELECT * FROM Users WHERE name = 'Alice';2. NoSQL (Non-Relational Databases)
NoSQL databases are document, key-value, graph, or wide-column stores. They do not use standard tabular relations.
- Structure: Flexible. Often looks like JSON or Dictionaries.
- Schema: Dynamic. You can add fields on the fly; different documents in the same collection can have different fields.
- Scalability: Typically Horizontal (adding more servers/sharding).
- Examples: MongoDB (Document), Redis (Key-Value), Cassandra (Wide-Column), Neo4j (Graph).
- Best For: Rapid prototyping, big data, real-time applications, content management systems.
Example (MongoDB / JSON Style)
// A "User" document
{
"_id": 1,
"name": "Alice",
"email": "alice@example.com",
"hobbies": ["reading", "coding"], // Arrays are easy here
"address": { // Nested objects are easy
"city": "New York",
"zip": "10001"
}
}3. Summary Comparison
| Feature | SQL | NoSQL |
|---|---|---|
| Data Model | Tables & Relations | Documents, Key-Value, Graphs |
| Schema | Pre-defined (Rigid) | Dynamic (Flexible) |
| Scaling | Vertical (Bigger Server) | Horizontal (More Servers) |
| Joins | Complex & Powerful | Limited or handled in code |
4. ACID vs BASE
When choosing a database, you often choose between two consistency models:
ACID (SQL)
Prioritizes consistency and reliability.
- Atomicity: All or nothing transactions.
- Consistency: Data is always valid.
- Isolation: Transactions don't interfere.
- Durability: Data is saved permanently.
BASE (NoSQL)
Prioritizes availability and performance over immediate consistency.
- Basically Available: The system guarantees availability.
- Soft state: The state of the system may change over time, even without input.
- Eventual consistency: The system will eventually become consistent once it stops receiving input.
5. Database Triggers
A Database Trigger is a set of SQL statements stored in the database catalog. A trigger is executed or fired automatically whenever an event associated with a table occurs, such as an INSERT, UPDATE, or DELETE.
- Automatic Execution: Triggers cannot be called manually; they run automatically when the event happens.
- Use Cases:
- Auditing: Automatically logging who changed a row and when.
- Validation: Enforcing complex constraints that cannot be defined by standard check constraints.
- Derived Data: Automatically updating a
TotalSalescolumn in aUserstable whenever a new row is added toOrders.
Pros and Cons
- Pros: Ensures data integrity rules are enforced consistently, regardless of which application accesses the database.
- Cons: Logic is hidden in the database (hard to debug), can cause performance issues if complex, and can lead to cascading triggers (Trigger A fires Trigger B...).