Skip to content
LLD Learn/Advanced Transactional Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Money, Precision & Double-Entry Ledgers

5 min read

You'll learn to

  • -Show with a running example why money must never be stored as a floating-point number
  • -Explain a double-entry ledger as a see-saw whose two sides always add up to zero, and detect a broken entry
  • -Make a retried payment safe with an idempotency key, and compute a tiered commission step by step

Financial schemas tolerate subtle bugs less than almost any other kind, because the failure is literally wrong money. That makes this chapter's techniques, exact number types and a double-entry structure, load-bearing and not optional polish.

Why DECIMAL, Never FLOAT, for Money

Computers store ordinary fractional numbers (floating point) in binary, and most decimal fractions, such as 0.1, have no exact binary form. It is like trying to write one third in decimal: you get 0.3333 and never quite arrive. For money, those tiny errors add up. Run the demo and watch them appear, then see the fix of counting whole cents as integers.

Press Run: the floating-point totals are slightly off, and the integer-cents total is exact

In a database the equivalent of integer cents is DECIMAL (also called NUMERIC), which stores an exact decimal value with a fixed number of digits. DECIMAL(12, 4) means up to 12 digits in total, 4 of them after the decimal point, so the largest value is 99,999,999.9999.

DECIMAL with explicit precision and scale
CREATE TABLE trades (
    id INTEGER PRIMARY KEY,
    symbol TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    price DECIMAL(12, 4) NOT NULL,      -- up to 12 total digits, 4 after the decimal point
    total_value DECIMAL(15, 2) NOT NULL -- currency amounts: 2 decimal places
);

The numbers are chosen for the data. A price needs enough decimal places for fractions of a cent, as real markets use. A total value uses the standard two decimal places of a currency amount. Naming these numbers explicitly, instead of writing a bare DECIMAL with defaults, shows you thought about what precision this data needs.

Double-Entry Ledger: Every Transaction Balances

Think of a see-saw. When money moves, it leaves one account and arrives in another. Nothing is created or destroyed. Double-entry bookkeeping records exactly that: every transaction is two balanced entries, a debit from one account and a matching credit to another, for equal amounts. This is not tradition for its own sake. It is a structural check. At any moment, the entries of every transaction must sum to zero, so a corrupted or missing entry shows up instead of hiding.

Every trade produces two balanced ledger entries
CREATE TABLE ledger_entries (
    id INTEGER PRIMARY KEY,
    transaction_id INTEGER NOT NULL,    -- groups the two (or more) entries of one transaction
    account_id INTEGER NOT NULL REFERENCES accounts(id),
    amount DECIMAL(15, 2) NOT NULL,     -- positive for credit, negative for debit
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- A $5,000 trade: cash account debited, securities account credited, same transaction_id
-- INSERT INTO ledger_entries (transaction_id, account_id, amount) VALUES (501, :cash_account, -5000.00);
-- INSERT INTO ledger_entries (transaction_id, account_id, amount) VALUES (501, :securities_account, 5000.00);

-- integrity check: every transaction's entries must sum to exactly zero
SELECT transaction_id, SUM(amount) FROM ledger_entries GROUP BY transaction_id HAVING SUM(amount) != 0;

Idempotency Keys: Making Retries Safe

Suppose a payment succeeds on the server, but the confirmation is lost on the way back, so the client times out and retries. A naive server would charge the customer twice. Think of pressing an elevator button twice: the second press should not call a second elevator. An idempotency key is a unique label that the client creates once per logical operation and sends again with every retry of it. The server recognizes "I have already done this exact request" and returns the original result.

A UNIQUE constraint on the idempotency key makes retries safe
CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    idempotency_key TEXT NOT NULL UNIQUE,   -- client-generated, same value on every retry
    amount DECIMAL(15, 2) NOT NULL,
    status TEXT NOT NULL DEFAULT 'completed',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- A retried request with the same idempotency_key hits the UNIQUE constraint,
-- so the application catches that error and returns the original transaction
-- instead of creating (and charging) a second one.

The demo below plays both ideas together. A dictionary stands in for the UNIQUE constraint. The same request key arrives twice, and the ledger gets exactly two entries, not four. Then a deliberately corrupted write leaves one side out, and the zero-sum check catches it.

Press Run: a retry changes nothing, and a one-sided entry is detected

A UNIQUE constraint alone is necessary but not the whole story under real concurrency. Two retries arriving at the same instant could both check "does this key exist yet?" and both try to insert. The constraint still saves you: one insert succeeds and the other fails, and the application catches that specific error, looks up the existing row, and returns it.

Rules as Data: Tiered, Time-Varying Rates

Financial systems are full of rules that change on a schedule, such as commission percentages, fee tiers and interest rates. Hardcoding them means a deploy for every rate change. Store the rule as data, and add effective-dating columns, a start and an end time, so you can ask "what rate applied on this past date?" without any separate history mechanism.

Tiered rates as configurable, time-versioned data
CREATE TABLE commission_tiers (
    id INTEGER PRIMARY KEY,
    min_amount DECIMAL(15, 2) NOT NULL,
    max_amount DECIMAL(15, 2),                 -- NULL = no upper bound
    rate DECIMAL(5, 4) NOT NULL,
    effective_from TIMESTAMP NOT NULL,
    effective_until TIMESTAMP                  -- NULL = still in effect
);
-- "what rate applied to this bracket on March 15th?"
SELECT rate FROM commission_tiers
WHERE min_amount <= :amount AND (max_amount IS NULL OR :amount < max_amount)
  AND effective_from <= :as_of AND (effective_until IS NULL OR :as_of < effective_until);

A trade that spans several tiers is computed bracket by bracket, not with one flat rate. Say the brackets are 2% on the first $10,000, 1.5% on the next $40,000, and 1% on everything above $50,000. A $100,000 trade pays $200 on the first bracket, $600 on the second and $500 on the remaining $50,000, a total of $1,300.

The split between broker and firm is itself another effective-dated rule table, so a broker's share can change on promotion without rewriting old commissions. At 60 percent, the broker gets $780 and the firm $520. The commissions table stores the trade, the broker, the amounts, and a CHECK that broker share plus firm share always equals the commission exactly, down to the cent.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design the Trade Engine and Commission Tracking schemas in the LLD Lab's Wolf of Wall Street act.

Ready to Build This?

Modeling a ledger correctly is half the job - querying it to prove it balances is the other half. SQL Lab's "Bleeding Out" (SUM) is the same instinct as the integrity check in this chapter: aggregate a column to check whether the numbers add up to what they should.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!