The Volunteer Rota That Refuses to Sort
A community centre stores its volunteer rota in a single table. Each row is one shift, with fields: ShiftDate (entered as text like 05/03/2024), VolunteerName, Role, and HoursWorked. There are 412 rows. The coordinator wants to sort by ShiftDate to find the earliest shifts, but sorting alphabetically puts 05/03/2024 after 28/02/2024 because 0 sorts after 2. She also notices that when she types VolunteerName into a filter, some volunteers appear twice with slightly different spellings (J. Patel and Jay Patel). She asks whether she should restructure the database or just sort more carefully.
1. Explain why sorting the ShiftDate column gives the wrong order, and state the correct fix at the data-structure level rather than the sorting level.
Read the worked answer
The column holds dates as text, so sorting compares characters left to right, not points in time. 05/03/2024 sorts after 28/02/2024 because the first character 0 is compared with 2. Sorting 'more carefully' cannot fix this; the underlying data type is wrong.
The fix is to store the date in a Date/Time field so the database stores it as a serial number and sorts chronologically. In practice:
- Add a new Date/Time field (e.g.
ShiftDateCorrect). - Populate it by converting the text, using the correct day/month order for the locale (here
05/03/2024= 5 March). - Verify a sample of rows against the original text.
- Once verified, remove or hide the text field so it cannot be used by mistake.
This is a schema decision, not a sorting trick.
2. The duplicate names (`J. Patel` / `Jay Patel`) are the same person. What does this tell you about the table design, and what is the minimal structural change that would prevent it recurring?
Read the worked answer
The rota table stores the volunteer's name redundantly on every shift row, so the same person can be spelled differently in different rows. That is a repeating-data / normalisation problem: the volunteer's identity is not held in one place.
The minimal fix is to split the volunteer into its own table:
Volunteers(VolunteerID primary key, FullName, ContactDetails)Shifts(ShiftID, ShiftDate, VolunteerID foreign key, Role, HoursWorked)
Each volunteer now has exactly one name record; the rota references it by VolunteerID. Spelling variants become a single correction in one row rather than a hunt across 412 rows. The VolunteerID foreign key also enforces that every shift points to a real volunteer.
3. Before restructuring, the coordinator wants to count total hours per volunteer. Given the current messy table, what should she do first, and why does the order matter?
Read the worked answer
Clean and consolidate identities before aggregating. If she groups by VolunteerName now, J. Patel and Jay Patel will produce two separate totals, each understating the real figure.
Order of operations:
- Identify duplicate identities (e.g. group by name and inspect near-matches).
- Standardise to one spelling per person, or map both to a single
VolunteerID. - Then run the aggregate:
SUM(HoursWorked)grouped by the volunteer key.
Aggregating first and 'fixing' afterwards means the totals are wrong and must be recomputed. Clean the key, then aggregate.