Twelve stale addresses and one lost fine in a lending library

Three classic anomalies, plus a concurrency problem, all evidenced in the scenario.

Update anomaly. MemberAddress is repeated on every loan row. One address change should be one edit; instead it is as many edits as the member has rows, and the evidence is that 12 rows kept the old address. The same applies to MemberName and MemberPhone. The underlying problem is that a fact about the member is stored as if it were a fact about the loan.

Delete anomaly. Deleting the member's 8 rows to remove the member also removed 8 loan records, one ₹360 fine and the trace of an unreturned book. Member and loan are fused in one row, so deleting one destroys the other. A member who has history should normally be marked inactive, never deleted.

Insert anomaly. A new member cannot be added until they borrow a book, and a new title cannot be added until someone borrows it, because a row cannot exist without a loan. That is why the catalogue and membership grow only as a side effect of lending.

Lost update. Two volunteers editing separate copies and mailing them means the last save wins; there is no way to see which rows were changed in the other copy, and no audit of who changed what.

Derived data by hand. Fine sits as a typed value. A fine is a calculation from DueDate, ReturnDate and a rate, so typing it guarantees arithmetic errors and an inability to recompute when the rate changes.

What changes. Each fact gets one home: member details once in a Members table, book details once in a Titles table, physical copies in a Copies table, and one row per borrowing event in a Loans table that points to a member and a copy. Fine becomes a query over the loan, not a typed number, unless it is waived or paid, in which case the payment is its own record. Address changes then touch one row; deleting a loan no longer touches the member.

Tables and keys.

• Members(MemberID PK, Name, Phone, Address, JoinDate, Status)
• Titles(TitleID PK, Title, Author, Category)
• Copies(CopyID PK, TitleID FK, Condition, Status) - needed because one title has several physical copies, and the fine and the lost-book record depend on which copy moved.
• Loans(LoanID PK, CopyID FK, MemberID FK, IssueDate, DueDate, ReturnDate)
• FineRules(RuleID PK, Category, RatePerDay, MaxFine) or a single rate row if the library charges uniformly.
• Payments(PaymentID PK, LoanID FK, Amount, PaidDate) - records money actually collected, so a waived fine is distinguishable from an unpaid one.

Relationships. One member to many loans; one title to many copies; one copy to many loans over time, but at most one open loan per copy, enforced by a check that ReturnDate is null for only one row per CopyID. MemberID and CopyID are foreign keys into Loans; Loans is the only table that grows with lending.

Overdue list. A query joining Loans and Members where ReturnDate is null and DueDate is before today, returning member name, phone, title, copy number, days overdue. Because only around 90 loans are open, this is small and instant; the join is on indexed keys rather than a text match on a book title, which is what makes it reliable.

Fine statement. For each member, sum over open or returned loans the days late times the daily rate, capped by MaxFine, minus payments recorded for that loan. Group by MemberID and order by name. Because the amount is computed, a rate change recomputes correctly and a typing error is impossible.

Why this is faster than the spreadsheet. The librarian's under-a-minute requirement is met not by a cleverer formula but by the fact that the overdue query touches 90 rows, not 6,200, and the fine is derived rather than typed. Both reports become saved queries that run on demand.

A password on the file solves the wrong problem. It does not stop the lost update - both volunteers still hold separate copies and the last save still wins, silently discarding the other person's work. It does not stop the delete anomaly - deleting a member's rows still destroys the loan history. It creates one shared secret that everyone knows, so nothing can be attributed to a person, and if the password is lost the library loses the data. It also does not stop copying: anyone who opens it can save a copy elsewhere with no password. And it protects the file while doing nothing for the two reports, which remain error-prone manual work.

Minimum acceptable option if she stays with spreadsheets.

1. Split the workbook into three sheets by kind of fact: Members (one row per member, MemberID as key), Titles and Copies, and Loans holding only IDs and dates. Enter member details once.
2. Replace typed names and titles in Loans with lookup or validation lists on MemberID and CopyID, so the same member cannot be spelled three ways.
3. Compute Fine with a formula from DueDate, ReturnDate and a rate cell, never typed.
4. Enforce single-editor discipline: one master file on one laptop, a named editor for each day, and a dated copy taken at closing time. If the second volunteer needs to work, they work on a different day, not on a different copy.
5. Retire members with a Status column instead of deleting rows.

This keeps the library on a tool it knows while removing the update, delete and lost-update problems. The honest caveat: it still relies on human discipline for the single-editor rule, so at 6,200 loans a year a small desktop database (Access, LibreOffice Base or a hosted MySQL with a form) is the better next step, and the split sheets make that migration straightforward.