SQL vs NoSQL Databases

Modern distributed platforms deploy a **Polyglot Persistence** pattern, routing different business models to the optimal database engines:

• Storage Paradigm Classifications: Structuring persistence bounds:

• Database Indexing & Query Optimization: Optimizing point lookup speeds in SQL.
• Sharding & Database Partitioning: Spreading SQL databases horizontally.
• Consistent Hashing: Hash rings in Cassandra Wide-Column clusters.

ACID vs BASE Paradigms

Databases represent two opposing design philosophies on the CAP theorem spectrum:

1. ACID (SQL Baseline)

• Atomicity: Entire transaction succeeds or rolls back.
• Consistency: Database transition matches all schema constraints.
• Isolation: Concurrent transactions do not bleed into each other.
• Durability: Committed transactions survive power crashes (via WAL log flush).

2. BASE (NoSQL Baseline)

• Basically Available: The database prioritizes accepting write commands over immediate synchronization.
• Soft State: Node replica values can drift over time without active lock enforcement.
• Eventual Consistency: Replicas eventually sync to store identical states after update streams stop.

Select ACID when consistency is absolute; choose BASE when write availability is the scaling priority.

Access-Pattern-First Data Modeling

In SQL interviews, candidates often start with entity diagrams (User, Post, Comment) and hope JOINs will work at scale. In NoSQL and high-scale SQL designs, flip the process: **list every read and write query first**, then design tables around those access paths.

1. Start from Queries, Not Entities

Write down the hot paths before drawing schemas: "fetch user timeline by user_id sorted by created_at DESC", "lookup order by order_id", "list products in category X under $50". Each distinct query pattern may need its own physical storage layout — one normalized table cannot serve all of them efficiently at billion-row scale.

2. Denormalize for Read Paths

If a feed query joins Users + Posts + LikeCounts on every page load, precompute and embed like_count and author_name directly into the Post row (or a feed-specific table). You pay extra write amplification on each like, but eliminate a three-table JOIN on every read — the classic read-heavy trade-off.

3. Composite Keys for Wide-Column Stores

In Cassandra or DynamoDB, the partition key determines which node holds the data; the sort key orders rows within that partition. Design the composite key to match your query: (user_id, created_at) for a per-user timeline, (category_id, price) for category-sorted product browse. A query that does not include the partition key triggers a full cluster scan — a design failure, not a tuning problem.

# Timeline table — one query, one table
PRIMARY KEY ((user_id), created_at, post_id)
→ SELECT * FROM timeline WHERE user_id = ? ORDER BY created_at DESC LIMIT 20

# Separate table for the inverse query (posts by hashtag)
PRIMARY KEY ((hashtag), created_at, post_id)

4. When to Duplicate Data Across Tables

• Different partition keys for the same entity: Store a user's posts under user_id for their profile and under hashtag for search — two write paths, zero JOINs at read time.
• Read replicas of hot fields: Copy display_name into every comment row so rendering a thread never hits the Users table.
• Materialized counters: Maintain a denormalized follower_count column updated asynchronously rather than COUNT(*) on every profile view.

Rule of thumb: duplicate when the read QPS on a JOIN path exceeds what a single node can serve, and the duplicated fields change infrequently or can tolerate brief staleness.