# 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)

```sql
-- 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)

```json
// 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 `TotalSales` column in a `Users` table whenever a new row is added to `Orders`.

### 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...).

[[programming/dictionaries-and-maps]]