I85PREP
⌘ K
KASNEB DICT study materials
KASNEB DICT study resources

Duplicate Patients and a Backup Window That Never Closes

KASNEB DICT · Put your knowledge to work in context.

All case studies

Duplicate Patients and a Backup Window That Never Closes

A three-branch clinic runs a patient records database holding 45,000 patient rows. Reception staff at each branch register patients independently, and a recent audit found roughly 6,000 rows that appear to be duplicates of other rows, some with conflicting dates of birth. The database has no unique constraint on national ID because many patients do not supply one. A nightly full backup of the 380 GB database to a second disk inside the same server starts at 23:00 and finishes around 05:40, overlapping the 05:00 start of the morning batch that posts daily billing. The clinic's stated requirement is that no more than 15 minutes of billing data may be lost, and that the system must be restored within 4 hours. Last month a disk failure took the server down for two days.

1. Design a backup and recovery arrangement that meets the 15-minute and 4-hour targets, and show why the current arrangement fails both.

Read the worked answer

Why the current setup fails

  • RPO 15 minutes is not met. A nightly full backup means up to 24 hours of billing data can be lost. Nothing captures changes during the day.
  • RTO 4 hours is not met. Restoring a 380 GB full backup from disk takes hours, and last month's outage took two days because there was no surviving copy: the backup sat on a second disk in the same server, so the disk failure destroyed both the data and the backup.
  • It also causes an operational fault. The backup runs 23:00-05:40 and collides with the 05:00 billing batch, so both jobs slow each other and the batch may read a database mid-backup.

Proposed arrangement

  1. Weekly full backup on Sunday, off-peak.
  2. Nightly differential after the billing batch completes, not before it.
  3. Transaction log backups every 10 minutes to meet the 15-minute RPO with margin.
  4. Ship copies off the server: one to a NAS in a different building, one to encrypted cloud storage. The 3-2-1 rule is what last month's incident proved was missing.
  5. Test restores quarterly and record the elapsed time; an untested restore is an assumption, not a control.

RTO check. A full restore plus differential plus log replay must be rehearsed and timed. If measured time approaches 3 hours, keep a warm standby or restore only the affected filegroup.

2. How would you resolve the 6,000 suspected duplicate records, and what schema change prevents recurrence without breaking registration for patients with no national ID?

Read the worked answer

Resolution approach (do not mass-delete)

  1. Block and match rather than eyeball. Group candidates by surname sound, date of birth and phone number, then score pairs. Records matching on all three are near-certain duplicates; those matching on two need human review.
  2. Survivorship rules. Where dates of birth conflict, prefer the value supported by the earliest clinical encounter, not the most recently edited record, because edits are where errors enter. Merge clinical history into the surviving record; retain the loser record as a merged alias pointing to the survivor so old references still resolve.
  3. Never hard delete. A merged-away patient ID may be referenced by prescriptions, invoices or lab results. Keep the ID, mark it inactive, point it to the survivor.
  4. Review in batches per branch with reception supervisors, because the staff who registered the patients can interpret local naming conventions.

Schema change without breaking national-ID-less registration

  • Add a nullable national_id column with a filtered unique index (unique where national_id is not null). This enforces one row per ID without rejecting patients who have none.
  • Add a mandatory deterministic match key, for example a computed hash of normalised surname + given name + date of birth + sex, with a unique constraint, plus a documented exception path with supervisor override.
  • Enforce at entry: a search-before-register screen that returns existing candidates. Prevention at the front desk is cheaper than 6,000 merges.

3. A billing clerk and a receptionist update the same patient's invoice at the same moment. Which transaction isolation behaviour would you specify, and what would you accept as a trade-off?

Read the worked answer

The failure to avoid is lost update: both users read a balance of 5,000, each adds a charge, and one write silently overwrites the other.

Specification

  • For the read-modify-write of a balance, use row-level locking with a SELECT ... FOR UPDATE inside the transaction, or an atomic UPDATE ... SET balance = balance + :amount. Either prevents lost updates regardless of the session's general isolation level.
  • Set the database default to READ COMMITTED rather than SERIALIZABLE. Read committed blocks dirty reads and, with explicit row locks on the contended rows, is sufficient here.

Reasoning. SERIALIZABLE across the whole database would satisfy correctness but would throw serialisation failures at reception desks during peak registration, and staff would see unexplained retries. Isolation should be raised only where the business rule requires it, not globally.

Trade-offs I accept

  • Read committed still permits non-repeatable reads, so a report run twice in one session may differ. Acceptable, because reports are point-in-time snapshots by design.
  • Row locks introduce brief waits and a deadlock risk if two transactions lock rows in different orders. Mitigate by always updating invoice lines before invoice headers, and by setting a short lock timeout with a user-visible retry message.