ACID vs BASE: Choosing the Right Database Model

ACID Databases (Relational)

Atomicity, Consistency, Isolation, Durability

Characteristics

• •✅ Strong consistency
• •✅ ACID guarantees
• •✅ Complex transactions
• •✅ Referential integrity
• •❌ Harder to scale horizontally
• •❌ Higher latency for distributed scenarios

Examples

• •PostgreSQL, MySQL, SQL Server, Oracle

When to Use ACID

// Financial transactions - NEED ACID
async function transferMoney(fromAccount, toAccount, amount) {
  await db.transaction(async (trx) => {
    // Must succeed or fail atomically
    await trx('accounts')
      .where({ id: fromAccount })
      .decrement('balance', amount);

    await trx('accounts')
      .where({ id: toAccount })
      .increment('balance', amount);

    await trx('transactions').insert({
      from: fromAccount,
      to: toAccount,
      amount,
      timestamp: new Date()
    });
  });
}

Use Cases:

• •💰 Banking and financial systems
• •📦 E-commerce orders and inventory
• •🎫 Booking systems (tickets, hotels)
• •📋 ERP and CRM systems
• •🏥 Healthcare records

BASE Databases (NoSQL)

Basically Available, Soft state, Eventually consistent

Characteristics

• •✅ High availability
• •✅ Easy horizontal scaling
• •✅ Low latency
• •✅ Flexible schema
• •❌ Eventual consistency
• •❌ No built-in transactions (limited)
• •❌ Application handles conflicts

Examples

• •MongoDB, Cassandra, DynamoDB, Firebase

When to Use BASE

// Social media posts - BASE is fine
async function createPost(userId, content) {
  // Write to main database
  const post = await db.collection('posts').insert({
    userId,
    content,
    timestamp: new Date(),
    likes: 0
  });

  // Async: Update user's post count (eventually consistent)
  setTimeout(async () => {
    await db.collection('users')
      .doc(userId)
      .update({ postCount: admin.firestore.FieldValue.increment(1) });
  }, 0);

  // Async: Update timeline feeds
  setTimeout(async () => {
    await updateFollowerFeeds(userId, post);
  }, 0);

  return post;
}

Use Cases:

• •📱 Social media feeds
• •📊 Analytics and logging
• •💬 Real-time chat
• •🎮 Gaming leaderboards
• •📰 Content management systems
• •🌐 Session storage

CAP Theorem

You can only have 2 of 3:

Consistency (C)

All nodes see the same data at the same time.

Availability (A)

Every request receives a response (success or failure).

Partition Tolerance (P)

System continues despite network failures.

Trade-offs

CP Systems (Consistency + Partition Tolerance)

• •MongoDB, HBase, Redis
• •Sacrifice availability during partitions
• •"Better to be unavailable than inconsistent"

AP Systems (Availability + Partition Tolerance)

• •Cassandra, DynamoDB, CouchDB
• •Sacrifice consistency for availability
• •"Better to serve stale data than be down"

CA Systems (Consistency + Availability)

• •Traditional RDBMS in single-node setup
• •Not realistic for distributed systems
• •Network partitions will happen

Eventual Consistency in Practice

// Like counter - eventual consistency is acceptable
async function likePost(postId, userId) {
  // Optimistic update in UI
  updateUIImmediately(postId);

  // Background: Update counter (may take seconds)
  await firebase.firestore()
    .collection('posts')
    .doc(postId)
    .update({
      likes: firebase.firestore.FieldValue.increment(1)
    });

  // Background: Add to likes collection
  await firebase.firestore()
    .collection('likes')
    .add({ postId, userId, timestamp: new Date() });
}

// User sees immediate feedback
// Actual consistency achieved in ~1-2 seconds

Hybrid Approaches

SQL Server with JSON

Use ACID database with flexible schema:

SET NOCOUNT ON;

CREATE TABLE [dbo].[Products] (
  [Id] INT PRIMARY KEY IDENTITY(1,1),
  [Name] NVARCHAR(255) NOT NULL,
  [Price] DECIMAL(10,2) NOT NULL,
  [Metadata] NVARCHAR(MAX)  -- JSON flexible attributes
);

-- Query JSON fields
SELECT
    [Id],
    [Name],
    [Price],
    JSON_VALUE([Metadata], '$.color') AS [Color]
FROM [dbo].[Products] WITH(NOLOCK)
WHERE JSON_VALUE([Metadata], '$.color') = N'blue'
OPTION (RECOMPILE);

-- Index JSON fields (computed column)
ALTER TABLE [dbo].[Products]
ADD [MetadataColor] AS JSON_VALUE([Metadata], '$.color') PERSISTED;

CREATE NONCLUSTERED INDEX [IX_Products_MetadataColor]
ON [dbo].[Products]([MetadataColor]);

MongoDB Transactions

NoSQL with ACID guarantees (MongoDB 4.0+):

const session = client.startSession();

try {
  await session.withTransaction(async () => {
    await ordersCollection.insertOne({ ... }, { session });
    await inventoryCollection.updateOne(
      { productId: 'xyz' },
      { $inc: { quantity: -1 } },
      { session }
    );
  });
} finally {
  await session.endSession();
}

Saga Pattern

Distributed transactions across services:

async function bookTrip(userId, flightId, hotelId) {
  let flightBooked = false;
  let hotelBooked = false;

  try {
    // Step 1: Book flight
    await flightService.book(flightId);
    flightBooked = true;

    // Step 2: Book hotel
    await hotelService.book(hotelId);
    hotelBooked = true;

    // Step 3: Charge payment
    await paymentService.charge(userId);

    return { success: true };
  } catch (error) {
    // Compensating transactions (rollback)
    if (hotelBooked) await hotelService.cancel(hotelId);
    if (flightBooked) await flightService.cancel(flightId);

    throw error;
  }
}

Decision Matrix

| Requirement | Choose ACID | Choose BASE | |------------|-------------|-------------| | Consistency is critical | ✅ Yes | ❌ No | | High write throughput needed | ❌ No | ✅ Yes | | Complex joins required | ✅ Yes | ❌ No | | Need horizontal scaling | ❌ Difficult | ✅ Easy | | Financial data | ✅ Yes | ❌ No | | Analytics/Logs | ❌ No | ✅ Yes | | Relationships between entities | ✅ Yes | ⚠️ Denormalize | | Schema changes frequently | ❌ No | ✅ Yes |

Best Practices

1. •Use ACID for core business logic - Orders, payments, inventory
2. •Use BASE for high-volume, less critical data - Logs, analytics, caching
3. •Consider polyglot persistence - Different databases for different use cases
4. •Understand your consistency requirements - Not everything needs strong consistency
5. •Test failure scenarios - How does your system behave during network partitions?

Conclusion

No "best" database model - choose based on requirements:

• •Need strong consistency? → ACID (PostgreSQL, MySQL)
• •Need massive scale? → BASE (Cassandra, DynamoDB)
• •Need both? → Hybrid (PostgreSQL with JSONB, MongoDB with transactions)