Money, Precision & Double-Entry Ledgers
You'll learn to
- -Store monetary values with DECIMAL, never float, and explain exactly what rounding bug that avoids
- -Design a double-entry ledger where every transaction balances, and add idempotency keys so a retried write never double-charges
Financial schemas have a lower tolerance for subtle bugs than almost any other domain, because the failure mode is literally incorrect money - which makes this chapter's two techniques (correct numeric types, and double-entry structure) load-bearing rather than optional polish.
Why DECIMAL, Never FLOAT, for Money
Floating-point numbers cannot represent most decimal fractions exactly in binary - 0.1 + 0.2 famously does not equal 0.3 in floating-point arithmetic. For monetary values, that imprecision compounds across enough transactions to produce real, incorrect balances. DECIMAL (or NUMERIC) stores an exact decimal value with a fixed precision and scale, avoiding this class of error entirely.
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 precision and scale are not arbitrary - price needs enough decimal places for fractional-cent pricing common in real markets, while total_value uses the standard 2 decimal places for a currency amount. Naming these numbers explicitly (not just "DECIMAL" with defaults) is worth doing out loud, since it shows you have thought about what precision this specific data actually needs.
Double-Entry Ledger: Every Transaction Balances
Double-entry bookkeeping records every transaction as two balanced entries: a debit to one account and a credit to another, always for equal amounts. This is not accounting tradition for its own sake - it is a structural correctness guarantee: at any point, summing all entries in the ledger must equal zero, which means a corrupted or missing entry is detectable, not silent.
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
A network timeout after a payment request succeeds on the server, but before the client receives confirmation, is a genuinely common failure mode - and a naive retry would charge the customer twice. An idempotency key (a unique identifier the client generates once per logical operation, sent with every retry of that same operation) lets the server recognize "I have already processed this exact request" and return the original result instead of processing it again.
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.A UNIQUE constraint alone is necessary but not quite sufficient for full idempotency safety under real concurrency - two retries arriving at the exact same instant could both check "does this key exist yet" and both attempt an insert. The UNIQUE constraint is still what saves you here: one insert succeeds, the other fails the constraint and the application catches that specific error to look up and return the existing row instead.
Rules as Data: Tiered, Time-Varying Rates
Financial systems are full of business rules that change on a schedule - commission percentages, fee tiers, interest rates - and hardcoding them in application code means every rate change needs a deploy. The fix uses the same instinct as everything else in this chapter: store the rule as data, not as code, and add effective-dating columns so you can ask "what rate applied on this specific past date" without a 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 multiple tiers (a $100,000 trade against 2% / 1.5% / 1% brackets) is computed by summing each bracket's contribution, not by applying one flat rate - and the split between broker and firm on that computed amount is itself another effective-dated rule table, so a broker's split percentage can change on promotion without rewriting historical commissions. The resulting commissions table stores trade_id, broker_id, trade_amount, commission_amount, broker_share, and firm_share, with a CHECK constraint that broker_share + firm_share always equals commission_amount exactly, down to the penny.
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.