Duplicate Patients at the Immunisation Camps
A district health society ran six immunisation camps and collected 1,840 paper registration forms. A volunteer entered everything into one LibreOffice Base table called CampData with columns FormNo, PatientName, Age, Sex, Village, CampDate, CampLocation, ScreeningResult and FollowUpDue. The same person often attended two or three camps, and the table now has 212 duplicate rows. Names vary: 'Sunita Devi', 'Sunita Devi ' and 'SUNITA DEVI'; some ages are blank; three villages are spelled two ways. The medical officer wants a list of people with FollowUpDue = Yes who did not attend any later camp in the same village, plus a count of unique patients per camp. The volunteer suggests using PatientName + Age as the primary key because 'everyone has a name'. Forms are still arriving for the last camp, and the officer needs the list before the next camp on Friday.
1. Propose a normalised table design, naming primary and foreign keys. Explain why PatientName + Age is a poor primary key and what anomalies the single table creates.
Read the worked answer
Use at least four tables: Villages(VillageID PK, VillageName), Patients(PatientID PK, FullName, Sex, BirthYearOrAge, VillageID FK), Camps(CampID PK, CampDate, CampLocation), and Screenings(ScreeningID PK, PatientID FK, CampID FK, ScreeningResult, FollowUpDue), with a unique constraint on (PatientID, CampID). PatientName + Age fails as a key because names are not unique, ages change, blank ages exist, and spelling variants would create a new 'person' each time. It would also allow the same person to be entered twice with different spellings, and it cannot distinguish two people with the same name and age in the same village. The single CampData table produces update anomalies (one village spelled two ways), insert anomalies (a camp cannot be recorded until a patient attends) and delete anomalies (removing the last patient deletes the camp information). Separating patients, camps and attendance also makes the follow-up query expressible without string matching.
2. Describe a safe procedure to merge the 212 duplicates and match new forms, including what to do when name and age match but the phone number differs.
Read the worked answer
Treat matching as a reviewable process, not a delete script. First copy CampData into a staging table and keep FormNo as a crosswalk. Standardise names (TRIM, remove extra spaces, convert to a consistent case, strip honorifics), map village spellings to VillageID, and parse ages. Block candidates by VillageID plus first three letters of the normalised name, then compare full name and age within one year. If a phone number is present, compare the last 10 digits; a phone match raises confidence, but a phone mismatch does not automatically prove a different person, because families share phones. Where name and age match but phone differs, mark 'probable' and send to a human reviewer with both source forms. Never auto-merge on name alone. After approval, create the PatientID, repoint all Screening rows, and archive the original rows. A false merge is worse than a duplicate because it attaches one person's screening result to another.
3. Write the logic for the follow-up defaulter list and the unique-patient-per-camp count. Note null-handling decisions.
Read the worked answer
Plain English: for each screening marked FollowUpDue = Yes at camp C, check whether the same PatientID has any later screening at a camp in the same VillageID. If not, the patient is a defaulter for that follow-up. Pseudocode:
for each s in Screenings where s.FollowUpDue = 'Yes':
p = s.PatientID; c = CampOf(s.CampID)
later = exists t in Screenings, c2 in Camps
where t.PatientID = p and c2.CampID = t.CampID
and c2.CampDate > c.CampDate
and c2.VillageID = c.VillageID
if not later: add (p, c.CampDate) to defaulters
For the count: SELECT CampID, COUNT(DISTINCT PatientID) FROM Screenings GROUP BY CampID. Null decisions: a null PatientID cannot be counted as a unique patient; put those rows in a review list. A null age does not block matching but lowers confidence, so do not use age as the sole match. If a patient has two follow-up-due screenings, report the earliest unresolved one and note the second, so the call list is not duplicated.