The Donor Spreadsheet That Counts Gifts Twice
Loaves and Fishes is a regional food bank. Donor and donation records live in a single spreadsheet with 12,400 rows, and each row repeats the donor's name, mailing address, phone, and pledge amount. When a donor moved in March, a volunteer updated the address on three rows but not the other 27, and the spring appeal went to two addresses. The same donor appears as M. Alvarez, Maria Alvarez, and Alvarez, Maria, and the annual total is reported three different ways depending on who filters the sheet. A new volunteer built a year-end statement by joining Donations to a Contacts table, where one donor can have several contact people, and the grand total came out about 2.4 times what the treasurer expects. The director wants a database that will not let this happen again but has no budget for a developer and needs something working before the December campaign.
1. Name the class of problem and propose a table design with keys. Explain what each split fixes.
Read the worked answer
These are update, insertion, and deletion anomalies caused by an unnormalized design: donor attributes are stored once per donation row, so a donor fact has as many copies as the donor has gifts. A move requires 30 coordinated edits, which is why three rows were changed and 27 were not, and a first-time donor who has not yet given cannot be recorded at all.
Design:
- Donors(DonorID primary key, FullName, AddressLine1, City, State, ZIP, Phone, Email), with a uniqueness rule on normalized name plus a contact detail to catch the Alvarez variants.
- Donations(DonationID primary key, DonorID foreign key, DonationDate, Amount, CampaignID, PaymentMethod).
- Campaigns(CampaignID primary key, Name, Goal).
- Contacts(ContactID primary key, DonorID foreign key, Name, Role, Email) kept only because one donor genuinely has several contact people.
The move becomes one update in Donors. The pledge amount now lives once per gift, so a total is a SUM over Donations and cannot disagree with itself. The foreign key blocks a donation with no donor, and data types block text in the Amount column. A desktop product such as Access or LibreOffice Base is enough at this scale and can be maintained by a volunteer.
2. The joined statement reports roughly 2.4 times the true total. Explain the mechanism, how to confirm it, and how to fix it.
Read the worked answer
This is fan-out. The join is one-to-many: one donation row matches several contact rows for the same donor, so the donation is repeated once per contact and SUM over the joined result counts it each time. 2.4 is approximately the average number of contact records per donor, which is why the inflation is proportional rather than random. If the query chains further into any other one-to-many table, the multiplication compounds.
Confirm by comparing COUNT() over the join against COUNT() over Donations alone, and by running the statement for a single donor whose gifts you know by hand. If the row counts differ, the sum is inflated.
Fix by aggregating before joining: compute SUM(Amount) grouped by DonorID in a subquery on Donations, then join that one-row-per-donor result to Donors and Contacts. Alternatively run the campaign total straight from Donations with no contact join at all. Resist SUM(DISTINCT Amount), which merely hides the bug and would collapse two legitimate $50 gifts into one. Add a standing reconciliation check: any published total must equal SELECT SUM(Amount) FROM Donations for the same date range.
3. Given no developer and a December deadline, what is the migration sequence, and what must be validated before the old sheet is retired?
Read the worked answer
Freeze and back up first. Declare the spreadsheet read-only, keep the original file untouched as an archive, and work on a copy. Retiring the source without a rollback copy is the one irreversible mistake available here.
Clean before import, not after. Standardize names (trim spaces, consistent case, separate first and last fields), then deduplicate using a written rule such as normalized name plus address or phone. Send ambiguous matches to a review queue rather than auto-merging: collapsing two real people destroys gift history and is far harder to undo than a duplicate. Assign DonorIDs only after the review clears.
Validate with arithmetic, not impressions. Record the source row count and SUM(Amount), then confirm the imported Donations table matches both exactly, and spot-check five donors end to end. A penny of difference means a lost or duplicated row.
Then prevent recurrence: require key fields, replace free-text donor entry with a lookup, and stop anyone editing the Donations table directly. Pilot with one small campaign before December, run the December mailing from the new system with the old sheet as backup, and write the dedupe rule into a one-page procedure so next year's volunteer applies the same standard.