Databases, data models, and ACID transactions

01What is a database, data model, and transaction?

A database is an organized collection of related data. A database management system (DBMS) is the software that stores, retrieves and updates it. In everyday engineering conversation, “database” often refers to the combined system. Its data model determines whether the application thinks in tables, documents, key-value pairs, or relationships. An access pattern is a concrete query or update, such as “find recent orders for customer U7.” An invariant is a rule that must remain true, such as “available stock never becomes negative.”

A transaction groups database operations so their changes commit together or roll back together. ACID names four properties: atomicity, ACID consistency, isolation and durability. They describe what commits together, which rules remain valid, how concurrent transactions interact and which failures saved data survives. Check the database’s guarantees against your reads and writes.

One database is a sensible starting point. As the service grows, its limit might be storage, popular-item contention, history reads, or expensive analytics. The label SQL or NoSQL does not identify which limit we have. First write the questions and rules; then choose a model and implementation that support them.

Assume this purchase must create the order and allocate stock together or do neither. We have not yet introduced independent payment and warehouse services. Keeping inventory and orders in one database lets us explain a local transaction before considering a workflow that spans separate services. The quantities and prices are assumptions for this example.

Derive the model from the required operations: conditional stock allocation, order lookup by ID, and customer history ordered by time. In the example, order O81 contains two MUG9 items at an assumed $12 each. Creating the $24 order changes available stock from five to three. That joint state transition defines the required transaction boundary.

02Relational data: tables, keys, joins, and constraints

A relational database represents facts as rows in tables. Columns name fields and usually assign their types. Relationships connect records through keys. Separate shared inventory from each order’s agreed commercial terms.

OrderLine retains the purchase price if tomorrow’s catalog price changes. This deliberate duplication preserves history; eliminating every repeated field is not the goal.

SQL is a language for querying and changing relational data. A join combines O81 with its lines. An index beginning with customer and then creation order supports customer U7’s order history. Constraints such as a unique order identifier or a valid customer reference enforce specific rules within the database’s supported scope.

A useful history index is (customer_id, created_at DESC, order_id DESC), supporting a query shaped like SELECT ... FROM Orders WHERE customer_id = 'U7' ORDER BY created_at DESC, order_id DESC LIMIT 20. The final key breaks equal-time ties. It does not make an unrelated full-catalog search cheap. Store money as integer minor units or an exact decimal plus currency; the illustrative $12 unit price can be 1200 cents, and two units total 2400 cents.

03Key-value, document, wide-column, and graph models

The order, its line items and the customer relationship can be represented in several models. The choice changes which related facts are stored together and which queries need additional lookups or indexes. Compare each alternative against the same two needs: fetch O81 as a complete order and list U7’s recent orders.

A key-value store retrieves a value by a key, such as order:O81 → complete order data. That fits exact lookup. Listing every order for U7 needs another supported access path; one key does not automatically answer every question.

A document store can keep the order and its lines together: {id: O81, customer: U7, lines: [{sku: MUG9, qty: 2, price: 12}]}. This makes a complete-order read natural. Shared inventory remains separate because many orders refer to MUG9. Convenient embedding does not automatically make that cross-document rule atomic.

Wide-column systems organize application queries around partition keys, which select a group of records, and clustering keys, which order records within that group. They differ from analytical columnar engines that scan selected columns over many rows. Graph storage is useful when traversals are central, not merely because two records are related.

Choose by both fit and the operation that becomes awkward. A key-value layout needs a separate access path for customer history. A document layout makes one bounded order aggregate easy, but very large embedded arrays and shared inventory need another strategy. A wide-column layout favors planned partition-key queries and can concentrate a very large customer partition. A graph model makes multi-hop traversal expressive, but a relational foreign key alone does not justify adding a graph engine.

04SQL versus NoSQL: choose from workload and constraints

Schema is the agreed structure and meaning of records. A relational schema can enforce types and constraints. A flexible document schema can permit different shapes, but the application still needs rules for quantity, currency, and missing fields. Either model needs a compatible plan when old and new software versions coexist.

NoSQL describes a broad family, not one query language or consistency setting. Different systems expose document queries, keyed operations, graph languages, or SQL-like syntax. Some support multi-record transactions; MongoDB documents transaction support.

For this purchase, choose a relational database with appropriate transaction support. The reasons are the shared stock/order rule and useful history queries. We pay for indexes, contention on a hot product, and operating the database. We are not assuming that relational databases cannot distribute or that document stores cannot transact. If search or analytics requires another engine, treat it as a derived view of committed orders with a stated freshness delay and rebuild path.

05ACID: atomicity, consistency, isolation, durability

A transaction groups changes under specified guarantees. For O81, begin the transaction, reduce MUG9 stock by two only if at least two remain, insert the matching order and line, then commit. If a required step fails, roll back the transaction.

Keep the meanings separate

Rules involving several records may require stronger isolation or explicit locking. “ACID” does not mean every default isolation mode prevents every anomaly. The transactions-and-isolation chapter develops those traces; PostgreSQL’s isolation reference describes actual engine behavior. Application logic must still express the right invariant.

The transaction also records which logical purchase it is performing. purchase_key stays the same across retries; request_hash summarizes a consistently normalized request so the same key cannot silently mean different quantities or items. A row lock prevents competing updates to the same inventory row from proceeding simultaneously. That protection lets the database wait for an earlier updater and then test whether stock is still sufficient.

Here is the key part of a PostgreSQL-style transaction, assuming the tables and their uniqueness/foreign-key constraints already exist:

BEGIN;
INSERT INTO Orders
  (order_id, customer_id, purchase_key, request_hash, total_cents, currency)
VALUES ('O81', 'U7', 'purchase-71', 'hash-of-canonical-request', 2400, 'USD');
UPDATE Inventory
SET available = available - 2
WHERE sku = 'MUG9' AND available >= 2
RETURNING available;
-- Continue only if exactly one row was returned; otherwise ROLLBACK.
INSERT INTO OrderLine
  (order_id, sku, quantity, unit_price_cents)
VALUES ('O81', 'MUG9', 2, 1200);
COMMIT;

The comment is an application decision, not SQL that automatically aborts. Also enforce UNIQUE(customer_id, purchase_key) and a nonnegative-stock constraint. At PostgreSQL Read Committed, a competing updater waits for the row lock and rechecks its predicate against the updated row. Starting from two units, T1 changes 2 → 0 and commits; T2 then finds available >= 2 false and must roll back its transaction, removing the order it inserted earlier in that attempt. Starting from five, the single purchase changes 5 → 3. More complex multi-row rules still need the stronger strategy described above.

Claim the unique purchase key before allocating stock, as in this SQL order. A duplicate waits for the first transaction and recovers its existing outcome even if the successful purchase exhausted inventory. The new order remains uncommitted until all steps succeed; an insufficient-stock rollback removes it too. Performing the stock check first without resolving a prior purchase could incorrectly return out-of-stock for a retry of an already successful order.

06Unknown commits and changing transaction boundaries

Large images belong in storage suited to media bytes and delivery, with authoritative metadata references. Analytics can scan a derived store so monthly reports do not crowd out purchases. Add those paths when requirements and measurements justify their maintenance cost. Each derived view needs committed input, a freshness policy, and a recovery mechanism.

Two concurrency failures also require a fresh attempt. A serialization failure means the database cannot safely commit the attempted concurrent execution under its isolation rules. A deadlock occurs when transactions wait on one another’s held resources in a cycle; the database aborts an attempt to break that cycle. In either case, the application must reevaluate the purchase from fresh reads.

If the purchase key already exists, roll back the whole attempt. Then read the earlier order and its request hash in a new transaction. In this example, the duplicate is detected before stock is allocated. Return the saved result only if the normalized request matches. After a serialization failure or deadlock, retry the whole transaction with the same purchase ID and a retry limit, not just the final INSERT.

07Interview answer: choose a database for orders

Interviewer: “Would you use SQL or NoSQL for orders?”

Candidate: “I would list the queries and atomic rules first. The workload needs O81 by ID, recent orders for U7, and a purchase changing shared inventory from five to three while creating the matching order. A relational model with indexes and a suitable transaction is a straightforward starting point.

“A document can make complete-order reads convenient, but inventory is shared across many orders. I still need a supported transaction, or an explicit stock-reservation workflow, to coordinate that change with order creation. I would not claim one family always scales better or always lacks transactions. If history reads dominate later, I can add a read model. If inventory becomes an independent service, I must redesign the workflow.”

The answer connects a storage choice to the work the product performs and names what would make us reconsider. That is more useful than choosing from vendor slogans or treating a flexible schema as permission to skip data modeling.

Recall the key ideas

Answer from memory before opening each card. Explain why the choice works and what it costs. Revisit missed cards tomorrow.