esc
  • WorkflowsPages
  • Case studiesPages
  • Machine Learning FoundationsLearn
  • AI EngineeringLearn
  • AI Guardrails & Red TeamingLearn
  • UI/UXLearn
  • Design SystemsLearn
  • Production System DesignLearn
  • Book a callContact
  • Download ResumeContact
Let’s talk

I’ll build the agent for it.

Databases: Where a System Keeps Its Data and Why the Choice Matters

What a database does for a system, how ACID transactions protect orders and payments, and how to think about the main database families.

In short

A database is the part of a system that holds on to data, and ACID transactions keep changes such as orders and payments correct together.

11 min readFree

Start Here: What You Will Learn

Almost every app must remember things after the screen closes: accounts, orders, messages, and settings. The part of a system that keeps this memory is the database. In this lesson you will learn what a database does for a system, why some changes must happen all together or not at all, how the main database families differ, and how to choose between them without following hype.

Key words

Database
A system that stores data, lets an application read and write it, and lets it ask questions about the data.
Durable
Lasting. Durable data is still there after the program stops, the server restarts, or the power fails.
Query
A question you ask the database, such as 'show me all orders from today'.
Transaction
A group of steps that the database treats as one unit. Either all the steps happen or none of them do.
ACID
A set of four promises that a database can make about transactions. The letters stand for atomicity, consistency, isolation, and durability.
Relational database
A database that stores data in tables with rows and columns, and usually uses the SQL language for queries.
NoSQL database
A family of databases that do not use the classic table model. It covers key-value, document, wide-column, and graph databases.
Application layer
The part of the system that runs the app's business rules. It sits between the users' requests and the database.

What Does a Database Do for a System?

A database is the part of a system that holds on to information. One beginner guide describes it as a core element for storing persistent data, which means data that outlives a single run of the program. You could keep data in a variable or a plain file, so why bring in a database? Because a variable disappears when the program stops, and a plain file offers no safe way for many users to read and change the same data at the same time.

A database adds three jobs on top of simple storage. Each job solves a problem that a plain file or variable handles badly.

  • Durable storage

    Data is written so it survives crashes and restarts. A user's order should still exist after the server reboots.

  • Structured queries

    You can ask precise questions, such as all unpaid orders for one restaurant, without reading every record by hand.

  • Safe concurrent access

    Many users can read and write at once, and the database keeps those actions from corrupting each other's work.

What Are the Four ACID Promises?

When a database handles transactions, it uses the ACID idea. ACID is a set of four promises. Relational databases such as PostgreSQL describe their transactions with these same four words. The promises matter most when a change touches more than one record, such as moving money from one account to another.

  • Atomicity: all or nothing

    A transaction either completes fully or has no effect. If a failure stops it halfway, the steps already done are cancelled, so no half-finished change stays behind.

  • Consistency: the rules still hold

    A change must leave the data valid. The database checks rules you define, such as a balance that may not go below zero, before it accepts the change.

  • Isolation: others do not see the mess

    When several transactions run at once, one of them should not see another's unfinished work. Each one sees a clean view, as if it ran alone.

  • Durability: committed means kept

    Once the database confirms a transaction as committed, the change is stored permanently, even if the server crashes a moment later.

Tip

Keep the four words in mind as a checklist. Atomicity is about failure in the middle, isolation is about other users at the same time, and durability is about what happens after the confirmation.

Try It: A Transfer That Fails Halfway

The sample below uses Python's built-in sqlite3 module, which keeps the whole database in memory so nothing is written to a file. It moves 100 from account A to account B. In the first run, the code 'crashes' after taking money out of A and before putting it into B. The rollback undoes the first step. In the second run, both steps succeed and the commit keeps them.

Python
import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER)")
db.execute("INSERT INTO accounts VALUES ('A', 500), ('B', 200)")
db.commit()

def transfer(crash):
    try:
        db.execute("UPDATE accounts SET balance = balance - 100 WHERE name = 'A'")
        if crash:
            raise RuntimeError("server crashed")
        db.execute("UPDATE accounts SET balance = balance + 100 WHERE name = 'B'")
        db.commit()
    except RuntimeError:
        db.rollback()

transfer(crash=True)
print(db.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())
transfer(crash=False)
print(db.execute("SELECT name, balance FROM accounts ORDER BY name").fetchall())

Output

[('A', 500), ('B', 200)]
[('A', 400), ('B', 300)]

Read the first output line. Account A still has 500, not 400. The half-finished subtraction never became visible, which is exactly what atomicity promises. The crash here is only simulated by an exception, but the idea is the same for a real server failure: without a commit, the database does not keep the partial change.

Why Do Relational Databases Use Transactions for Money and Orders?

Relational databases are the usual choice when a set of changes must be correct together. A classic example from the PostgreSQL manual is a bank transfer, where the sender's balance must drop by exactly the same amount as the receiver's balance rises. The manual also points out that other people reading the data should never see the debit without the credit. A transaction gives you that guarantee.

  1. 1

    Begin the transaction

    Tell the database that the next group of changes belongs together. In SQL this is usually written as BEGIN.

  2. 2

    Make every change

    Write each row that the business action needs, such as the order, the payment record, and the restaurant ticket.

  3. 3

    Commit if all went well

    Confirm the group. From this point the changes are permanent and visible to others as one unit.

  4. 4

    Roll back if anything failed

    Cancel the group. Every change made since the start is thrown away, as if it never happened.

Tip

In PostgreSQL, a single statement on its own is already wrapped in a small transaction. You only need an explicit BEGIN and COMMIT when several statements must succeed or fail together.

What Are the Main Database Families?

Databases come in several families. Each family makes different tradeoffs between strict rules, flexible shapes, and how well it spreads across many machines. The word NoSQL covers all the non-table families. It means 'not only SQL' rather than a single design. Here is a short map. Each family gets its own lesson later in this course, so treat these cards as a first sketch.

  • Relational

    Data lives in tables with fixed columns, and the database enforces links between tables. It supports ACID transactions across rows and tables and works well for orders, payments, and bookings.

  • Key-value

    Each item is stored under one key, such as a user ID. Lookups by that key are very fast, but questions that search by other fields are hard.

  • Document

    Each record is stored as one whole document, such as a JSON-like object, and documents do not have to share the same fields. This keeps one item's data together in one place.

  • Wide-column

    Data is organized into rows that can hold different sets of columns. It is built for very large amounts of data spread across many machines.

  • Graph

    Data is stored as nodes (things) and edges (connections between them). It suits questions that follow links, such as friends of friends.

A common myth says that NoSQL means no transactions. The Towards Data Science source on ACID says that NoSQL solutions generally cannot comply with ACID, with graph databases as the exception. Others relax some ACID promises to gain scale and availability. The safe rule is to check each product's own guarantees instead of assuming them from the family name.

Tip

Pick by data shape and access pattern, not by hype. Ask two questions: what does the data look like, and how will the app read and write it most often? A tool that is popular this year may still be the wrong fit for your data.

Worked Example: Placing a Food Order

Imagine a food-delivery app, similar to Uber Eats, and a customer who taps Place order. This is a design sketch to show the idea. It is not a description of how any real company stores its data. The app's business rules run in the application layer, which sends the work to the database as one transaction.

  1. 1

    Start one transaction

    The application opens a transaction before it writes anything. Every write below belongs to this one unit.

  2. 2

    Write the order

    A row is added to an orders table with the customer, the restaurant, and the items.

  3. 3

    Write the payment

    A row is added to a payments table. If the payment is declined, the app treats the whole group as failed.

  4. 4

    Write the restaurant ticket

    A row is added so the restaurant's screen can show the new order.

  5. 5

    Commit or roll back

    If the payment succeeded, the app commits. If the payment failed, it rolls back, and no order or ticket remains. The customer never sees a ghost order that nobody can pay for.

The 'recently viewed restaurants' list works differently. It only needs one lookup: given a user ID, return the last few restaurants that user opened. Nobody's money depends on it, and no other table must change in step with it. A key-value store fits this shape well, and it can be faster and simpler than a relational table for this one job. If the list is briefly stale, the customer only sees a slightly old list.

Tip

Ask one question for each piece of data: what goes wrong if this write fails halfway? If the answer is a wrong bill or an order with no payment, put the writes in one transaction. If the answer is a slightly old list, a simpler store is enough.

Check Your Understanding

Quick check

A bank app moves $100 from Account A to Account B. The server crashes right after it subtracts $100 from A, but before it adds $100 to B. Which database property should stop the money from vanishing, and how does it do that?

Quick check

A small app shows each user's last five opened restaurants. The app only ever asks for one user's list, by user ID, and no other data must change at the same time. Which store fits best?

Quick check

A food app shows the customer a confirmed order. A second later, the power fails on the database server. After the restart, which promise says the confirmed order must still be there?

Key Takeaways

A database keeps data durable, answers structured queries, and lets many users read and write safely. For changes that must stay correct together, such as orders and payments, relational databases use transactions that follow the ACID promises. Other families trade some of those rules for flexible shapes, simple lookups, or fast link-following. Choose the family from your data's shape and your app's most common reads and writes. The next lessons in this course go deeper into the choices this overview introduces.

Tip

When you meet a new database in a design, write down three things first: the data's shape, the most common access pattern, and the failures that would hurt most. Those three answers usually point to the right family.

Sources

How is this lesson?

Last updated on 17 November 2026

Learn with me

Every lesson is free. I add new ones as I learn. Have a question, or want help with your own work? Book a short call.