Distributed & Object-Relational DBs: Architectures, Models & Real-World Use

TAKEAWAYS:

• Distributed databases split data across nodes for scalability, fault tolerance, and locality, but introduce challenges like transaction atomicity and data consistency (CAP theorem).
• Object-Relational (OR) databases bridge relational tables with object-oriented features (e.g., inheritance, polymorphism) using techniques like mapping classes to tables or storing objects as BLOBs.
• Architectural trade-offs: Centralized systems simplify management but fail under load; distributed systems scale but require replication strategies (master-slave, multi-master) and consistency models (strong vs. eventual).
• Real-world examples: eSewa uses distributed databases to handle high transaction volumes across Nepal; NEPSE’s stock trading system relies on eventual consistency for real-time updates.
• Normalization vs. denormalization: Distributed systems often denormalize to reduce joins, while OR databases use hybrid schemas (e.g., PostgreSQL’s JSONB).
• Exam focus: Compare architectures, explain 2-phase commit for distributed transactions, and design an ER diagram for a multi-branch bank with distributed branches.

1. Distributed Database Systems: Why and How?

Distributed databases store data across multiple physical locations (nodes) connected by a network. Unlike centralized databases, they enable:

• Scalability: Handle growth by adding nodes (e.g., Google’s Spanner).
• Fault tolerance: Failures in one node don’t crash the entire system (e.g., Ncell’s CDN for call records).
• Locality: Process data closer to users (e.g., Daraz’s regional warehouses syncing inventory in real time).

Key Architectures

How Data is Distributed

1. Partitioning (Sharding):

   • Split data horizontally (by rows) or vertically (by columns).
   • Example: NEPSE’s stock data is partitioned by exchange (e.g., NEPSE vs. OFINS).

   flowchart TD
     A["User Query: Stock Prices"] --> B["Router"]
     B --> C["Shard 1: NEPSE Stocks"]
     B --> D["Shard 2: OFINS Stocks"]
     C --> E["PostgreSQL Node 1"]
     D --> F["PostgreSQL Node 2"]

2. Replication:

   • Copy data to multiple nodes for redundancy.
   • Strategies:
     • Master-Slave: One node writes; others read (e.g., WhatsApp’s message logs).
     • Multi-Master: All nodes can write (e.g., Google Docs collaborative editing).
   • Conflict Resolution: Use timestamps or last-write-wins (e.g., Pathao’s ride updates).

3. Allocation Strategies:

   • Centralized Allocation: One node decides where data goes (slow for large systems).
   • Distributed Allocation: Nodes autonomously decide (e.g., IPFS for decentralized storage).

Challenges in Distributed Databases

• CAP Theorem: Choose 2 out of 3:

  • Consistency (all nodes see same data).
  • Availability (system works despite failures).
  • Partition Tolerance (works during network splits).
  • Example: During a power outage in Kathmandu, Ncell might prioritize availability (keep calls working) over consistency (delayed billing updates).

• Transaction Management:

  • 2-Phase Commit (2PC): Ensures all nodes agree on a transaction (used in banking).

    sequenceDiagram
      participant Coordinator as Transaction Coordinator
      participant Node1 as Node A
      participant Node2 as Node B
      Coordinator->>Node1: Prepare()
      Coordinator->>Node2: Prepare()
      loop If both nodes agree:
        Node1-->>Coordinator: Yes
        Node2-->>Coordinator: Yes
        Coordinator->>Node1: Commit()
        Coordinator->>Node2: Commit()
      else
        Coordinator->>Node1: Abort()
        Coordinator->>Node2: Abort()
      end

  • Problems: If the coordinator fails, the system may deadlock (e.g., a bank transfer stuck between two branches).

• Data Consistency Models:

  • Strong Consistency: All reads return the latest write (e.g., bank balances).
  • Eventual Consistency: Updates propagate over time (e.g., WhatsApp status updates).
  • Causal Consistency: Preserves causality (e.g., if A sends money to B, B’s balance updates before A’s).

2. Object-Relational Databases: Bridging Tables and Objects

Relational databases struggle with complex data types (e.g., images, graphs, JSON). Object-Relational (OR) databases extend SQL to support:

• Object-oriented features: Classes, inheritance, polymorphism.
• Complex data: Arrays, JSON, XML, spatial data (e.g., maps in Pathao).

Why Use OR Databases?

Object-Relational Mapping (ORM) Techniques

1. Table-Per-Class (TPC):

   • Each class maps to a table.
   • Example: Employee class → employees table.
   • Problem: Doesn’t handle inheritance well.

2. Table-Per-Concrete-Class (TPC):

   • Each subclass gets a table with a discriminator column.
   • Example:

     CREATE TABLE payment_method (
       id SERIAL PRIMARY KEY,
       type VARCHAR(20), -- 'credit_card', 'mobile_wallet'
       -- common fields
     );
     CREATE TABLE credit_card (
       id INTEGER REFERENCES payment_method(id),
       card_number VARCHAR(16),
       expiry_date DATE
     );
     

3. Table-Per-Hierarchy (TPH):

   • One table for the hierarchy, with a discriminator.
   • Example:

     CREATE TABLE employee (
       id SERIAL PRIMARY KEY,
       emp_type VARCHAR(20), -- 'full_time', 'part_time'
       salary NUMERIC,
       hours_worked NUMERIC -- NULL for full_time
     );
     

4. Table-Per-Type (TPT):

   • One table for the superclass + one per subclass.
   • Example:

     erDiagram
       EMPLOYEE ||--o{ FULL_TIME : "has_a"
       EMPLOYEE ||--o{ PART_TIME : "has_a"
       EMPLOYEE {
         int id PK
         string name
         string emp_type
       }
       FULL_TIME {
         int id PK, FK
         numeric salary
       }
       PART_TIME {
         int id PK, FK
         numeric hourly_rate
       }

Real-World OR Database Examples

1. PostgreSQL (NEPSE’s Stock Data):

   • Uses JSONB to store dynamic attributes of companies (e.g., {"sector": "tech", "market_cap": 1000000000, "tags": ["growth", "high_risk"]}).
   • Query:

     SELECT company_name, market_cap
     FROM companies
     WHERE companies.data->>'sector' = 'tech';
     

2. Oracle (Bank Loan Processing):

   • Models loan types (home, car, personal) as inheritance:

     CREATE TYPE loan_type AS OBJECT (
       type_name VARCHAR2(20),
       interest_rate NUMBER
     );
     CREATE TABLE loans (
       loan_id NUMBER PRIMARY KEY,
       amount NUMBER,
       l_type loan_type
     );
     

   • Use Case: A bank in Nepal can apply different interest rates to different loan types dynamically.

3. SQL Server (Pathao’s Ride Data):

   • Stores geospatial routes as GEOGRAPHY type:

     CREATE TABLE rides (
       ride_id INT PRIMARY KEY,
       start_location GEOGRAPHY,
       end_location GEOGRAPHY,
       distance FLOAT
     );
     -- Query: Find all rides within 5km of Thamel
     SELECT * FROM rides
     WHERE start_location.STDistance(GEOGRAPHY::Point(85.3240, 27.7046, 4326)) < 5000;
     

3. Distributed vs. Object-Relational: When to Use Which?

Worked Example: Designing a Distributed OR Database for a Multinational Bank Scenario: A bank with branches in Kathmandu, Delhi, and Dubai needs to:

1. Store customer accounts (relational).
2. Handle geospatial loan approvals (OR).
3. Support real-time fraud detection (distributed).

Solution:

erDiagram
  CUSTOMER ||--o{ ACCOUNT : "has"
  ACCOUNT ||--|{ TRANSACTION : "records"
  BRANCH ||--o{ ACCOUNT : "hosts"
  LOAN ||--|| CUSTOMER : "issued_to"
  LOAN {
    int loan_id PK
    string type "home|car|personal"
    geography property_location
    json additional_terms
  }
  CUSTOMER {
    int id PK
    string name
    json contact_details
  }
  BRANCH {
    int branch_id PK
    string location
    geography coordinates
  }

Distribution Strategy:

• Partition by region: Kathmandu accounts on Node 1, Delhi on Node 2, etc.
• Replicate fraud rules: All nodes get the latest fraud detection model (eventual consistency).
• OR Features:
  • Store loan terms as JSONB (e.g., {"early_payoff_penalty": 2.5, "collateral": "house"}).
  • Use GEOGRAPHY for property locations in loan approvals.

In the Real World

1. eSewa’s Distributed Payment System:

   • Architecture: Client-server with master-slave replication for transactions.
   • How it uses this unit:
     • Distributed: Payment requests are routed to the nearest data center (Kathmandu, Pokhara, or virtual nodes).
     • Consistency: Uses 2-phase commit to ensure money is deducted from your account and added to the merchant’s before confirming.
     • Problem Solved: During the 2022 Nepal blackout, eSewa’s distributed nodes in Pokhara kept working while Kathmandu’s servers were down.

2. NEPSE’s Stock Trading Database:

   • OR Features: Stores company profiles as JSON with dynamic fields (e.g., {"pe_ratio": 15.2, "dividend_yield": 0.03, "esg_score": 78}).
   • Distributed: Shards data by exchange (NEPSE vs. OFINS) and time (intraday vs. historical).
   • Real-Time Updates: Uses eventual consistency for stock prices (a 1-second delay is acceptable for traders).

3. Pathao’s Ride-Matching Algorithm:

   • OR: Stores driver locations as GEOGRAPHY points and ride routes as polylines.
   • Distributed: Uses peer-to-peer for driver availability updates (no single bottleneck).
   • Example Query:

     -- Find all available drivers within 3km of Thamel
     SELECT driver_id, ST_Distance(
       driver_location::geography,
       ST_MakePoint(85.3240, 27.7046)::geography
     ) AS distance_km
     FROM drivers
     WHERE status = 'available'
     AND ST_Distance(driver_location::geography,
       ST_MakePoint(85.3240, 27.7046)::geography) < 3000;
     

Exam Tip

1. Architecture Questions:

   • Always compare centralized vs. distributed by listing pros/cons (e.g., "A multinational company should use distributed because...").
   • For CAP theorem, pick one real-world example (e.g., "During a power cut, Ncell prioritizes availability over consistency").

2. OR Database Design:

   • Draw an ER diagram with inheritance (use ||--o{ in Mermaid for "is-a" relationships).
   • Map classes to tables: Show how a PaymentMethod superclass becomes tables in TPC/TPH/TPT.

3. Worked Examples:

   • Distributed: Given a bank with 3 branches, partition by branch ID and replicate fraud rules.
   • OR: Given a Vehicle class with Car and Bike subclasses, use TPH with a discriminator column.

4. Common Pitfalls:

   • Don’t confuse replication with partitioning: Replication copies data; partitioning splits it.
   • ORM isn’t just about SQL: Explain how inheritance maps (e.g., "In TPC, each subclass gets a table with a foreign key to the superclass").

5. Past Exam Patterns:

   • Short Answer: Define 2-phase commit, eventual consistency, or sharding.
   • Long Answer: Design a distributed OR database for a given scenario (e.g., "A university with campuses in Kathmandu and Pokhara").

stateDiagram-v2
  [*] --> DistributedDB: Start
  DistributedDB --> Centralized: Single Site
  DistributedDB --> ClientServer: Multiple Sites
  DistributedDB --> PeerToPeer: Decentralized
  Centralized -->|Failure| Down
  ClientServer -->|Load Balanced| Scalable
  PeerToPeer -->|Consensus Needed| Complex