Dairy Collection Register: Normalising a Flat Table Without Losing Payment History
Shriram Dairy's collection centre writes every pour into one table: ReceiptNo, Date, FarmerName, Mobile, Village, Fat%, Litres, Rate, Amount. About 60 farmers pour twice a day, so a farmer's name and mobile number repeat roughly 700 times a month. Last month a farmer changed his mobile number; the clerk updated 41 rows but missed 3, so a payment SMS went to a stranger. In another case, deleting the only entry of a farmer who had stopped supplying also deleted the record that he had ever supplied. The secretary found two rows where Amount did not equal Litres x Rate. The centre now wants a proper relational design in a free DBMS, wants the receipt to remain the unique proof of collection, and wants past payments to stay correct when the rate chart is revised. The clerk insists on keeping a single table because 'joins are difficult'.
1. Identify the anomalies and the functional dependencies in the flat table, then propose a 3NF design with keys.
Read the worked answer
Functional dependencies in the flat table.
- ReceiptNo -> Date, FarmerName, Mobile, Village, Fat%, Litres, Rate, Amount (a receipt determines the whole row).
- FarmerName -> Mobile, Village (a partial dependency, since it depends on the farmer, not on the receipt).
- Fat% + Date -> Rate (the rate chart changes with fat content and with the revision date).
- Litres x Rate -> Amount (derived attribute).
Anomalies observed. Update: the mobile number had to be changed in about 700 rows a month, and 3 were missed. Deletion: removing a farmer's last receipt destroyed the only record of that farmer. Insertion: a new farmer cannot be registered until his first pour. Plus heavy redundancy of name, mobile and village.
3NF design.
- Farmer(FarmerID PK, Name, Mobile, Village)
- RateChart(Fat%, DateFrom, Rate) with PK (Fat%, DateFrom) - versioned, not overwritten
- Collection(ReceiptNo PK, FarmerID FK, Date, Shift, Fat%, Litres)
Amount is not stored as a free-standing column. It is computed in a query or view as Litres x Rate, with the rate selected from RateChart on the collection date. ReceiptNo alone is the key, which is what allows two pours in one day; (FarmerID, Date) would wrongly block the second pour.
2. The clerk objects that a single table is simpler and joins are difficult. Evaluate that objection using the numbers in the case, and state what you would concede.
Read the worked answer
Cost of keeping one table. At roughly 700 pours a month, name, mobile and village are duplicated about 8,400 times a year. One mobile number change is 1 row in Farmer versus up to 700 rows a month in the flat table, and the case already shows the failure: 41 rows updated, 3 missed, a payment SMS sent to a stranger. That is a data-integrity failure with a customer-visible consequence, not a theoretical one. Deletion lost the farmer's history, and two rows drifted on Amount because the same fact was stored in more than one place.
Is the join really difficult? No. Producing the clerk's familiar layout is one statement: SELECT c.ReceiptNo, f.Name, f.Mobile, f.Village, c.Fat, c.Litres, r.Rate, c.Litres*r.Rate AS Amount FROM Collection c JOIN Farmer f ON f.FarmerID = c.FarmerID JOIN RateChart r ON r.Fat = c.Fat AND c.Date >= r.DateFrom ... (with the latest DateFrom not after c.Date). With indexes on the join columns a free DBMS such as MySQL or LibreOffice Base returns a month of data instantly, and the same join can be saved as a view so the clerk keeps typing the old, simple SELECT *.
What to concede. One table is genuinely simpler for a one-person, single-user register with no concurrency and a weekly need. But this centre has two shifts, a rate chart that is revised, and payment messages going outside; the missed-SMS incident is the evidence that the simplicity has already been paid for.
3. The centre wants Amount to be reliable and past payments to remain correct when the rate chart is revised. Advise on whether to store Amount and on how rate revisions should be handled.
Read the worked answer
Storing Amount: the trade-off. Amount is a derived attribute (Litres x Rate). If it is stored as a plain column it can drift from its inputs - the case already has two rows where it does not match - and every correction to Litres or Rate must remember to update it. Options, in order of robustness:
- Do not store it; compute it in a view. Nothing can drift.
- If it must be stored for printing or audit, store it plus the Rate actually applied on that receipt, and add a CHECK constraint Amount = Litres * Rate (or a trigger). Then the row is self-consistent and the rate is frozen at the moment of collection.
Rate revisions. Do not overwrite RateChart.Rate; that would silently rewrite history and change last month's payments. Use effective dating: each row is (Fat%, DateFrom, Rate). To revise, insert a new row with the new DateFrom; close the old period by adding DateTo, or simply pick the row with the greatest DateFrom that is not later than the collection date. Past receipts keep pointing at the rate that was in force on their date, and the report for any past month can be reproduced exactly.
Judgement. Freeze the rate and (if stored) the amount at collection time. Recomputed historical amounts are acceptable for analytics but never for a payment record, because the farmer has already been paid on the old figure.