Section 5: Two Tables That Must Never Meet - Data Modeling
Executive Summary: Most data models are judged by the joins they make easy. This one is judged by a join it makes impossible. We lay out the ten tables of the college election, then focus on two: participation, which records that Alex voted, and ballots, which records a vote with no way back to Alex. We cover what each must not contain, how a shuffled flush hides arrival order, the honest limit of it all, and the election lifecycle that decides which writes are legal.
The interviewer points at the API you just finished. "You return a tracking code and you say the server forgets who voted. Show me the tables."
You: "The question is how we record that Alex voted without recording how Alex voted. Most of the schema is ordinary. Two tables carry the secret ballot, and what matters most is a column neither one has."
The Ten Tables
The data is small. Northfield has 8,000 eligible students; at 60% turnout that's 4,800 ballots of about 1 KB each, roughly 4.8 MB for the whole election. Size never drives this design; a unique constraint and a multi-row transaction do. So it lives in one managed relational database, single region, multi-AZ, with a synchronous standby. A NoSQL or distributed store would add scale we don't need and remove the two guarantees we do. Nothing at college or university scale flips that choice; at national scale there is no central ballot store at all (Section 11).
| Table | Key columns | Purpose |
|---|---|---|
elections |
election_id, name, opens_at, closes_at, status | One election and its lifecycle state |
races |
race_id, election_id, method, seats | President, VP, treasurer, two referenda |
candidates |
candidate_id, race_id, display order | Options per race |
ballot_styles |
style_id, election_id, race list | Which races a voter sees |
roll |
voter_id, election_id, ballot_style, eligible | Imported from the registrar |
participation |
voter_id, election_id, voted_at, idempotency_key_hash | That someone voted |
ballot_buffer |
random id, election_id, ballot_style, selections, received_day | Ballots waiting to be shuffled |
ballots |
ballot_id, election_id, ballot_style, selections, batch_id | What was voted |
bulletin_board |
election_id, tracking_code_hash | Public "is my code here?" lookup, no ballot content |
audit_log |
actor, action, target, at | Admin actions, never ballot content |
In v1 every student gets the same 5-race ballot, so ballot_styles has one row. It earns its place in Section 8, when the university brings about 32 styles. There is also no counters table: no running tallies during voting, for the reasons in Section 3.
Sign in to continue reading
The rest of this lesson is available with a free account. Signing in with Google or Microsoft is free.
Sign in to read the full lesson