Money, Precision & Double-Entry Ledgers
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.
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.
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.
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.
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.
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.
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.
Design the Trade Engine and Commission Tracking schemas in the LLD Lab's Wolf of Wall Street act.
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.
No comments yet. Be the first to start the thread!