Tech+: Eight Thousand Donors in One Spreadsheet

Eight Thousand Donors in One Spreadsheet

A regional food bank tracks about 8,000 donor households in one spreadsheet. Columns include Household Name, Address, Email, Donation Date, Amount, Food Item Donated, Volunteer Hours, and Notes. Staff type the same household five or six different ways - 'Smith Family,' 'The Smiths,' 'smith fam.' - and addresses get reformatted on every import. Last quarter two volunteers each recorded the same $500 pledge, so the annual report overstated cash receipts by $500. A grant application now requires total cash donations per household per calendar year, and the board must be able to show that no single donor exceeded a disclosure threshold in any one year. The food bank has one part-time staff member who knows Excel formulas and no database administrator.

1. Explain in database terms why this spreadsheet produces these errors, and describe the structures that would prevent them.

Read the worked answer

Diagnosis

No primary key. Identity is carried by a free-text name, so 'Smith Family' and 'smith fam.' are different rows as far as the software is concerned. The same real household becomes many records; there is no way to state that two rows are the same entity.
Mixed granularity in one row. A row holds a donation event, a donated food item, volunteer hours, and contact details simultaneously. A household with three cash gifts and two volunteer shifts cannot be represented without repeating the address five times, which is exactly how the name and address divergence starts.
No integrity constraints. Nothing rejects a duplicate pledge, a negative amount, a missing date, or a donation attributed to a household that does not exist.

Target structure

household(household_id PK, display_name, address, email)
donation(donation_id PK, household_id FK, donation_date, amount, donation_type, receipt_no)
food_item(item_id PK, description) and food_donation(donation_id FK, item_id FK, quantity)
volunteer_shift(shift_id PK, household_id FK, shift_date, hours)

Constraints: NOT NULL on household_id and donation_date; CHECK (amount > 0); foreign keys on every reference; UNIQUE(receipt_no) so the same pledge cannot be entered twice. In the data-entry form, the household is chosen from a pick list, never typed.

2. Write the query the grant application needs - total cash donations per household per calendar year - and explain the design choices in it.

Read the worked answer

SELECT h.display_name,
       EXTRACT(YEAR FROM d.donation_date) AS gift_year,
       SUM(d.amount) AS total_cash
FROM household h
JOIN donation d ON d.household_id = h.household_id
WHERE d.donation_type = 'cash'
GROUP BY h.household_id, h.display_name, EXTRACT(YEAR FROM d.donation_date)
ORDER BY gift_year, total_cash DESC;

Why each part matters

WHERE donation_type = 'cash' - the grant asks about cash only. Without this filter, donated food would be summed as if it were money, or volunteer hours would be double-counted.
Filter the rows before aggregating - this is a row filter, so it belongs in WHERE, not in a HAVING clause on the sum.
GROUP BY h.household_id, not by name - two genuinely different households can share a display name. Grouping on the key keeps them apart; the name is selected only for readability.
EXTRACT(YEAR …) - calendar year, per the grant's definition, rather than a rolling twelve months. State this explicitly in the report so there is no ambiguity.
JOIN, not LEFT JOIN - households with no gifts should not appear as zero rows in this report; use LEFT JOIN only if a complete list is needed for a different purpose.

For the disclosure threshold, the same query with HAVING SUM(d.amount) > <threshold> returns exactly the donors the board must look at, with the year attached.

3. What migration and reconciliation steps are needed before the report can be trusted, and what should be done about the historical $500 double-count?

Read the worked answer

Before loading

Copy the source file and freeze it read-only; all cleaning happens on a copy.
Capture baseline totals: row count and SUM(Amount) for the current period, so the post-migration numbers can be reconciled rather than assumed.
Deduplicate households by a deterministic rule (normalized name + address + email), but route ambiguous matches to a human. With 8,000 records and one part-time staffer, plan for a few hundred review decisions, not a one-click fuzzy match.
Assign a surrogate household_id to every accepted cluster and keep a mapping table from old row → new key so every original record is traceable.

The historical $500

Do not silently delete the duplicate. Mark one row as voided with a reason code and an audit note, so the correction is defensible if the grant auditor asks why the figure changed.
Reconcile: prior reported total minus $500 must equal the new total for that quarter. Investigate any other gap.
Check whether the overstatement was already reported to a funder; if so, a corrected figure should be sent, not quietly adjusted.

After loading

Run the per-household-per-year query and spot-check the ten largest donors by hand against source records.
Only then enforce UNIQUE(receipt_no) and CHECK (amount > 0) as hard constraints - adding them before cleanup will simply reject rows.
Give the staff member a data-entry form backed by pick lists, and keep the spreadsheet as a read-only export, not the system of record.