Dining Dollars

Dining Dollars is a prepaid balance that students spend at school dining locations. A local high school wants it for its lunch program. Parents load money onto a student’s account with a credit card. Students pay at a register by tapping their ID card. Students and parents can see the balance and the spending history in a mobile app.

This chapter is about the server and the database behind the app. Here is what they must do:

  • When a parent reloads a student’s account, the system must charge the parent’s card through the payment processor and, if the charge succeeds, add the amount to the student’s balance.
  • When a student pays at a register, the system must subtract the purchase amount from the student’s balance and record the purchase.
  • The system must refuse a purchase that would take the balance below zero.
  • When a student or parent requests the balance, the system must return the current balance.
  • When a student or parent requests the history, the system must list every reload and purchase, newest first, with amount, location, and time.
  • The system must record every reload and every purchase permanently. A confirmed purchase must not be lost.

The payment processor is an outside system. The application sends it an amount and a card token and gets back success or failure. Once a card is charged, the application cannot undo the charge. It can only issue a separate refund.

The schema

students
  student_id         integer   primary key
  name               text      not null

accounts
  account_id         integer   primary key
  student_id         integer   foreign key -> students, unique, not null
  balance_cents      integer   not null, check balance_cents >= 0

locations
  location_id        integer   primary key
  name               text      not null

ledger_entries
  entry_id           integer   primary key
  account_id         integer   foreign key -> accounts, not null
  amount_cents       integer   not null
  kind               text      not null, check in ('reload', 'purchase')
  location_id        integer   foreign key -> locations, null
  occurred_at        timestamp not null

A few notes on the schema:

  • Every reload and every purchase is a row in ledger_entries. The amount is signed: positive for a reload, negative for a purchase. Rows are added and never changed or deleted. The history the app shows is this table, filtered by account and sorted by time.
  • Amounts are stored in cents as integers. Money is not stored as a floating-point number, because floating-point arithmetic rounds.
  • location_id is set for purchases and null for reloads, since a reload does not happen at a register.
  • Each student has one account, so student_id on accounts is unique.

Here is the schema diagram (attributes are omitted for clarity):

erDiagram
    direction LR
    students ||--o| accounts : has
    accounts ||--o{ ledger_entries : has
    locations |o--o{ ledger_entries : has

The balance

The balance of an account is the sum of amount_cents over its ledger rows. The schema also stores it as balance_cents on accounts. That is denormalized data, the same kind we keep on HopPress’s posts table.

It is stored for the same reason. The balance is read at every tap and every time the app opens, and it changes only when a student pays or a parent reloads. Reading one column is cheaper than summing the ledger each time. Every reload and every purchase now writes two things: a ledger row and a new balance.

The check constraint on balance_cents means the database refuses to store a negative balance, whatever the application does.