In system design interviews, the quality of your design and its ability to scale depends heavily on the database you choose.

Choosing the wrong database can lead to high latency, data loss, or even system downtime.

In this article, we will cover the 9 most common use cases that come up often in system design interviews and explore the best databases for each scenario.

1. Structured Data Requiring ACID Compliance

Consider a major online marketplace like Amazon or Flipkart, which processes millions of transactions daily.

Each order involves multiple interdependent operations:

• Selecting a product.

• Updating inventory.

• Deducting payment from the customer.

• Recording the sale for accounting and analytics.

These operations need strong consistency and ACID transactions to avoid any data anomalies or transaction failures.

• Atomicity ensures that if you fail to charge the customer’s credit card, you won’t ship the item.

• Consistency guarantees that the product count never goes negative if the system runs out of stock.

• Isolation prevents two customers from purchasing the last item at the exact same time.

• Durability ensures that once a payment is processed, you can’t lose that record if the server crashes the next second.

Recommended Database – Relational Database

When you need strict data consistency and a well-defined schema, relational databases like MySQL or PostgreSQL are often the best choice.

These databases are specifically designed to enforce relationships, constraints, and transactional guarantees.

2. Flexible Schema