The Farmers' Co-operative Spreadsheet
Kopano Farmers' Co-operative records produce deliveries in one spreadsheet. Columns are Date, Farmer Name, Village, Depot, Produce, Grade, Mass kg, Price per kg, Amount, Paid. There are 480 farmers, 3 depots, and 11 produce types. Names are typed in different ways, villages are sometimes misspelt, and two depots both use the code D1. The co-operative pays farmers monthly. Last month two farmers with the same name were paid twice, and a grade change was applied to every row for one produce instead of one delivery. The co-operative wants to add a farmer telephone number and a depot manager. The spreadsheet has 18,000 rows and takes 40 seconds to open; staff email copies to each other.
1. Design a relational schema with tables, keys, and relationships. Explain how the design prevents the double payment and the incorrect grade change.
Read the worked answer
Use a relational schema: Villages(VillageID PK, VillageName); Farmers(FarmerID PK, Surname, OtherNames, VillageID FK, Phone); Depots(DepotID PK, DepotName, Manager); Produce(ProduceID PK, ProduceName); Deliveries(DeliveryID PK, DeliveryDate, FarmerID FK, DepotID FK, ProduceID FK, Grade, MassKg, PricePerKg); Payments(PaymentID PK, FarmerID FK, PaymentMonth, AmountPaid, DatePaid, Method). Grade belongs in Deliveries because it varies by delivery, not in Produce. A unique DeliveryID prevents the same delivery being entered twice; a unique constraint on FarmerID plus PaymentMonth prevents duplicate monthly payments. Farmer names are not keys, so two people with the same name remain separate. Depot codes become DepotID values, so D1 cannot refer to two depots. Amount can be calculated as MassKg times PricePerKg, avoiding inconsistent totals.
2. Recommend a migration and data-quality plan, and explain why the database should replace emailed spreadsheet copies.
Read the worked answer
Before migration, clean the data in a copy: standardise farmer names, assign a FarmerID to each person, replace village spellings with VillageID values, and resolve the duplicate D1 depot codes. Import into tables in order: Villages, Depots, Produce, Farmers, Deliveries, Payments. Use validation rules for mass, price, date, and grade, and make FarmerID a required field. Reconcile the 18,000 deliveries against the spreadsheet totals before deleting anything. After migration, create a monthly payment query that groups deliveries by farmer and month, and check it against last month's manual payments. Keep the spreadsheet read-only for one season. The database will open faster because it stores data in indexed tables rather than repeated text, and staff should use one shared database with permissions instead of emailing copies that diverge.