Databases: SQL, NoSQL & Distributed Caching

Relational Database Systems (RDBMS) & Schema Design

The relational database model was formalized by Dr. Edgar F. Codd at IBM in 1970 based on first-order predicate logic and mathematical set theory. In a relational database, data is represented mathematically as relations (tables), where each row is a tuple (record) and each column is an attribute governed by strict domain data types.

PostgreSQL versus MySQL Storage Architecture

While both systems implement the Structured Query Language (SQL), their internal storage engines differ fundamentally:

• PostgreSQL: An advanced object-relational database engine utilizing Multi-Version Concurrency Control (MVCC) without undo logs. Updates create a new version of the row tuple with transaction metadata (xmin, xmax), allowing concurrent reads to never block writes and writes to never block reads. It features native support for unstructured JSONB, custom types, and extensions (PostGIS, pgvector).
• MySQL (InnoDB Engine): The default web standard engine. Implements row-level locking, an automated undo log for transaction rollback, and a clustered index architecture where primary key records are stored directly within the leaf pages of the primary B-Tree.

Primary Keys, Foreign Keys & Integrity Constraints

Relational schemas enforce strict data invariants at the hardware storage layer:

• Primary Key (PK): A unique, non-null attribute identifying each record. In modern cloud distributed systems, UUIDv7 (time-ordered Universally Unique Identifiers) or 64-bit BigInts are preferred over sequential 32-bit auto-incrementing integers to prevent enumeration attacks and support multi-master replication without key collisions.
• Foreign Key (FK): Establishes a referential link to a primary key in another relation. Enforces referential actions on deletion: ON DELETE RESTRICT (rejects deletion of parent), ON DELETE CASCADE (automatically wipes dependent records), or ON DELETE SET NULL.

-- Enterprise PostgreSQL E-Commerce Production Schema
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$')
);

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL,
    order_status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
    shipping_address JSONB NOT NULL,
    placed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) 
        REFERENCES users(user_id) 
        ON DELETE RESTRICT 
        ON UPDATE CASCADE
);

CREATE TABLE order_items (
    order_item_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID NOT NULL,
    sku VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
    CONSTRAINT fk_items_order FOREIGN KEY (order_id) 
        REFERENCES orders(order_id) 
        ON DELETE CASCADE
);

Data Manipulation, Relational Joins & Window Functions

SQL is a declarative language: the backend engineer specifies what data is desired, and the database query optimizer formulates the physical relational algebra execution plan.

Relational Join Topology

When queries span multiple entities, the relational engine matches tuples across foreign key boundaries:

• INNER JOIN: Returns only rows where there is a matching value in both relations (Mathematical intersection: $A \cap B$).
• LEFT OUTER JOIN: Returns all rows from the left relation, matched with corresponding rows from the right relation; fills missing right-side attributes with NULL.
• FULL OUTER JOIN: Combines all rows from both tables, populating nulls wherever matching criteria are absent ($A \cup B$).
• CROSS JOIN: Produces the Cartesian product of both relations ($N \times M$ rows).

-- Complex Analytical Query: Joins, Aggregation & Window Partitioning
SELECT 
    u.user_id,
    u.email,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0.00) AS lifetime_spend,
    -- Window Function: Calculate user rank within entire cohort
    DENSE_RANK() OVER (ORDER BY COALESCE(SUM(oi.quantity * oi.unit_price), 0) DESC) AS customer_rank
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.order_status = 'completed'
LEFT JOIN order_items oi ON o.order_id = oi.order_id
WHERE u.is_active = true
GROUP BY u.user_id, u.email
HAVING COUNT(DISTINCT o.order_id) >= 1
ORDER BY lifetime_spend DESC
LIMIT 50;

Database Indexing & Query Optimization

Without an index, querying a table containing 10,000,000 rows forces the database storage engine to perform a Sequential Scan (Table Scan), reading every disk block sequentially from storage into memory ($O(N)$ computational complexity).

B-Tree (Balanced Tree) Index Mechanics

The default index in PostgreSQL, MySQL, and Oracle is the B-Tree. A B-Tree is a self-balancing, multi-way search tree where:

1. Root & Internal Nodes: Contain ordered separator keys and memory pointers directing search operations down the tree.
2. Leaf Nodes: Reside at identical depth and form a doubly-linked list. Each leaf contains the indexed column key alongside a TID (Tuple Identifier / Row ID) indicating the physical disk block address of the complete table row.

Traversing a B-Tree executes in $O(\log N)$ time: finding a record in a 10,000,000-row table requires only 3 to 4 pointer hops!

The Composite Index Leftmost Prefix Rule

If an index is created on multiple columns: CREATE INDEX idx_orders_user_status ON orders(user_id, order_status), the B-Tree sorts primary data by user_id first, and by order_status second.
• A query filtering by WHERE user_id = '...' will use the index.
• A query filtering by WHERE user_id = '...' AND order_status = '...' will use the index.
• A query filtering only by WHERE order_status = '...' cannot use the index, because the tree is sorted primarily by user_id!

ACID Transactions & Concurrency Isolation Levels

A database transaction is a logical unit of work that must satisfy the four foundational ACID guarantees:

• Atomicity: All modifications within the transaction are successfully committed to disk, or all are completely rolled back. There is no partial state.
• Consistency: The transaction transitions the database from one valid state to another, preserving all schema invariants, unique constraints, and foreign keys.
• Isolation: Concurrent transactions execute without mutual interference. The system appears as though transactions executed serially.
• Durability: Once a transaction commits, its effects survive system crashes and hardware power outages via the Write-Ahead Log (WAL).

ANSI SQL Concurrency Isolation Levels & Anomalies

-- ACID Financial Fund Transfer Transaction with Row-Level Locking
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 1. Acquire pessimistic row lock on source account
SELECT balance FROM accounts 
WHERE account_id = 'acc_source_01' 
FOR UPDATE;

-- 2. Deduct funds from source account
UPDATE accounts 
SET balance = balance - 500.00 
WHERE account_id = 'acc_source_01' AND balance >= 500.00;

-- 3. Credit destination account
UPDATE accounts 
SET balance = balance + 500.00 
WHERE account_id = 'acc_target_02';

-- 4. Record audit ledger entry
INSERT INTO audit_ledger (from_acc, to_acc, amount, transferred_at)
VALUES ('acc_source_01', 'acc_target_02', 500.00, CURRENT_TIMESTAMP);

-- Atomically flush to Write-Ahead Log (WAL) and release locks
COMMIT;

Database Normalization: 1NF, 2NF, 3NF & BCNF

Database normalization is a formal mathematical process of decomposing tables to minimize data redundancy and prevent insertion, update, and deletion anomalies.

The Progressive Normal Forms

1. First Normal Form (1NF): All attributes must contain atomic (indivisible) values. No repeating groups or comma-separated lists within a single column. Every row must have a unique identifier (Primary Key).
2. Second Normal Form (2NF): Must satisfy 1NF, and must eliminate Partial Dependencies: every non-key attribute must depend on the entire candidate key, not a partial subset of a composite key.
3. Third Normal Form (3NF): Must satisfy 2NF, and must eliminate Transitive Dependencies: non-key attributes must not depend on other non-key attributes ($A \rightarrow B$ and $B \rightarrow C$). In simple terms: "Every attribute must depend on the key, the whole key, and nothing but the key, so help me Codd."
4. Boyce-Codd Normal Form (BCNF): A stricter extension of 3NF. A relation is in BCNF if and only if for every functional dependency $X \rightarrow Y$, $X$ is a superkey.

Object-Relational Mapping (ORM) & The N+1 Problem

Application code models systems using object-oriented graphs, classes, and inheritance. Relational databases model systems using two-dimensional tabular relations and foreign keys. This architectural mismatch is known as the Object-Relational Impedance Mismatch.

Enterprise ORM Ecosystem

• Prisma (Node.js / TypeScript): Modern declarative schema-first ORM. Compiles schema to native binary query engines with complete end-to-end type inference.
• Sequelize / TypeORM (JavaScript): Traditional Active Record and Data Mapper models for Node.js backends.
• SQLAlchemy (Python): Industry benchmark in Python, supporting both high-level ORM and raw SQL expression language.
• Hibernate (Java / JPA): Enterprise standard implementing unit-of-work, dirty-checking, and first/second-level session caching.

// 1. Prisma Declarative Schema Definition
// model Order {
//   id        String      @id @default(uuid())
//   userId    String
//   user      User        @relation(fields: [userId], references: [id])
//   items     OrderItem[]
// }

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query', 'warn', 'error']
});

// Eager Loading: Solves the N+1 problem in a single joined database query
export async function getOrderWithDetails(orderId: string) {
  return await prisma.order.findUnique({
    where: { id: orderId },
    include: {
      user: {
        select: { id: true, email: true, firstName: true }
      },
      items: true
    }
  });
}

NoSQL Architectures & Document Databases (MongoDB)

As internet applications expanded to support millions of concurrent users and unpredictable schema models, traditional relational engines faced horizontal scaling limits. NoSQL (Not Only SQL) systems trade strict relational constraints for horizontal partition tolerance and dynamic data flexibility.

The CAP Theorem & BASE Properties

Formulated by Eric Brewer, the CAP Theorem dictates that a distributed datastore can simultaneously guarantee at most two out of three properties:

• Consistency (C): Every read receives the most recent write or an error.
• Availability (A): Every non-failing node returns a response, without guaranteeing it contains the newest write.
• Partition Tolerance (P): The system continues to operate despite network packet loss or partitioned nodes. (In cloud networks, network splits are inevitable; distributed databases must choose between CP or AP).

NoSQL systems replace strict ACID guarantees with BASE:
• Basically Available: The cluster guarantees availability through replica failovers.
• Soft state: Data values may change over time without immediate user interaction due to background replication.
• Eventual consistency: Given no new writes, all replica nodes will eventually converge to identical data.

MongoDB Architecture: WiredTiger & BSON

MongoDB is a distributed document database. It stores records as BSON (Binary JSON) documents inside Collections. BSON extends JSON by adding support for explicit data types (Double, Int32, Int64, Decimal128, Date, BinData, ObjectId).

import { Schema, model, Document } from 'mongoose';

export interface IProduct extends Document {
  sku: string;
  title: string;
  price: number;
  attributes: Record<string, unknown>;
  tags: string[];
}

const productSchema = new Schema<IProduct>({
  sku: { type: String, required: true, unique: true, index: true },
  title: { type: String, required: true },
  price: { type: Number, required: true, min: 0 },
  // Dynamic Polymorphic Attributes (Unstructured NoSQL Strength)
  attributes: { type: Map, of: Schema.Types.Mixed },
  tags: [{ type: String, index: true }]
}, {
  timestamps: true
});

export const ProductModel = model<IProduct>('Product', productSchema);

In-Memory Caching Strategies & Redis Architecture

Reading from NVMe solid-state storage incurs microsecond latencies; querying relational indexes under heavy load introduces contention. Redis (Remote Dictionary Server) is an in-memory key-value data structure store delivering sub-millisecond responses directly from server RAM.

The Cache-Aside (Lazy Loading) Architecture

The gold-standard caching design pattern in backend distributed systems follows four sequential steps:

1. Application receives an incoming HTTP request for a resource.
2. Application queries Redis:
   • Cache Hit: Redis returns the serialized JSON payload. The database is never touched!
   • Cache Miss: Redis returns null.
3. On Cache Miss, application queries the persistent SQL/NoSQL database.
4. Application writes the queried result into Redis with a Time-To-Live (TTL) expiration timestamp, and returns the response to the client.

import Redis from 'ioredis';

export const redis = new Redis({
  host: process.env.REDIS_HOST || '127.0.0.1',
  port: Number(process.env.REDIS_PORT) || 6379
});

// Reusable Cache-Aside Wrapper Function
export async function getOrSetCache<T>(
  key: string,
  ttlSeconds: number,
  fetchFn: () => Promise<T>
): Promise<T> {
  // 1. Attempt Cache Hit
  const cachedData = await redis.get(key);
  if (cachedData) {
    return JSON.parse(cachedData) as T;
  }

  // 2. Cache Miss: Execute heavy persistent datastore query
  const freshData = await fetchFn();

  // 3. Populate Redis with automatic TTL expiration
  await redis.set(key, JSON.stringify(freshData), 'EX', ttlSeconds);

  return freshData;
}

Architectural Decision Matrix: When to Use SQL versus NoSQL

University & Industrial Examination Review