Voter data integrity and audit system

A MySQL practice database that uses constraints and triggers to enforce voting rules and track changes.

  • MySQL
  • SQL
  • Relational design
  • Triggers

Project overview

I built a relational database for a sample election. It links voter registrations to ballots and candidates to races, and records the selections on each ballot. The project uses database rules to catch invalid writes and audit tables to show who changed a record and when.

How the database works

Validate changesConstraints and BEFORE triggers check links, account access, and vote limits.

Record changesAFTER triggers log inserts, updates, and deletes in the matching audit table.

Intentional rejection tests

These cases describe the expected results for the local SQL. For each rejected write, the test checks that the target table stays unchanged.

  1. Repeat a registration number

    Insert a voter with an existing registration number from the authorized test account.

    Expected resultThe unique constraint rejects the row. This verifies that the registration number can identify only one voter record.

  2. Use a nonexistent voter ID

    Insert a ballot linked to a voter ID that is absent from voter.

    Expected resultThe foreign key rejects the ballot, which verifies that every ballot must refer to an existing voter.

  3. Insert a race from another account

    Try to add the sample Dog Catcher race from a session that can insert into the table but fails the account check.

    Expected resultThe trigger raises SQLSTATE 45000. The same insert from the authorized test account is the positive control.

  4. Exceed the vote limit

    In the one-vote Dog Catcher race, try to insert a second, different candidate on the same ballot.

    Expected resultThe first selection is allowed. The second raises SQLSTATE 45000 once the race’s limit is reached. Using a different candidate isolates the vote limit from the duplicate-pair constraint.

Audit verification

The audit triggers are designed to record the timestamp, connected MySQL user, and a description of the change. The textbook’s positive tests insert, update, and delete a valid ballot, then check ballot_audit for the corresponding entries. One example changes the ballot type to absentee and checks for the updated value in the audit description.

Sources

  • Rick Silva, MySQL Crash Course, Chapter 17, “Tracking Changes to Voter Data with Triggers,” pp. 277–300. See exercise 17-4 on p. 283, the account test on p. 286, and the vote-limit trigger on pp. 287–288.