Normalising a Pharmacy Database Under Retention Law
Harbour Pharmacy runs 14 branches across Malta and Gozo. Each branch uses a till application that writes to a single MySQL 8 database in a data centre. The schema has one table, prescriptions, with 41 columns storing patient name, ID card number, address, drug, dose, prescriber and branch as free text. There are 2.3 million rows and the table grows by about 900 rows a day. At month-end the regional report takes 47 minutes and often times out at the 60-second limit set on the reporting user. A data-quality audit found 1,840 pairs of rows that are the same patient entered with different spellings, and 610 rows where the drug name has trailing spaces. Last month a branch manager exported a CSV of all prescriptions to a personal webmail account because the report was too slow. Maltese law requires prescription records to be kept for ten years, after which they should be destroyed.
1. The 41-column prescriptions table is in first normal form only. Redesign it and justify each split, taking care not to make the month-end report slower than it already is.
Read the worked answer
Identify the dependencies first. Patient name, address and ID card number depend on the patient, not the prescription; drug name and strength depend on the drug; prescriber details on the prescriber; branch name on the branch. These are transitive dependencies, so the table is not in 3NF. That is why one patient can appear as 1,840 near-duplicates: the name is stored 2.3 million times and every copy can be typed differently, with no key to prove identity.
Target:
- patient(patient_id PK, ID card number UNIQUE, name, address)
- prescriber(prescriber_id, registration number, name)
- drug(drug_id, name, strength, form), with TRIM on load plus a lookup rather than free text for the 610 trailing-space rows
- branch(branch_id, name)
- prescription(prescription_id, patient_id FK, prescriber_id FK, drug_id FK, branch_id FK, dose, dispensed_at)
This gives the dedupe a key: match on ID card number exactly, then fuzzy-match names only for the residual. Most of the 1,840 should collapse.
The reporting objection is legitimate but usually mistaken. A four-way join from 2.3M fact rows to small dimension tables on indexed foreign keys normally beats scanning 41 wide redundant text columns, because I/O falls. Keep transactional tables normalised. If the report still exceeds 60 s, add a monthly summary table rebuilt by a scheduled job, not denormalised columns in the live table. Do not fix performance by widening the fact table again.
2. A branch manager emailed a full CSV of prescriptions to a personal webmail account. Analyse the failure and propose controls that would have prevented it without stopping legitimate reporting.
Read the worked answer
This is not primarily a user-behaviour problem, though the action was wrong. The system offered two options: wait 47 minutes for a report that times out, or take the raw data. When a control makes the legitimate task impossible, the workaround is predictable.
Layered controls:
- Design and performance: give each branch a parameterised report filtered on branch_id, running in seconds, exporting only needed columns. Data minimisation limits the harm even if data leaves.
- Access control: branch managers should not hold SELECT on the whole table. Use row-level security or branch-restricted views, with no access to the full table.
- Egress: block personal webmail at the proxy for this group, or apply DLP rules on ID card numbers and drug names, and alert on large CSV attachments.
- Audit: log every export with user, row count and time; review weekly.
Legally, this is a personal data breach under the Data Protection Act (GDPR-derived). The DPO must assess risk; full prescription data linked to ID card numbers is high risk. If not contained, notify the IDPC within 72 hours and inform affected individuals. Attempt recall, but assume it has been read.
Note the tension: blocking webmail before fixing the report pushes staff to photograph screens. Fix the task first.
3. Design the ten-year retention so records are destroyed lawfully without breaking the links the audit and accounts teams rely on.
Read the worked answer
Settle three ambiguities first: ten years from what event, deletion by whom, and what counts as destruction.
Clock: run the period from the last relevant event (last dispensing or last patient interaction), not the row insert date. Otherwise a long-term patient loses early records while still being treated.
Do not delete in place. Parent rows are referenced by dispensing, stock and payment records; a hard DELETE either fails on foreign keys or cascades and destroys data the accounts team must keep. Instead:
- Partition by year and move closed partitions to an archive table with the same keys.
- Where statistics are needed but identity is not, anonymise: drop name and ID card number, keep age band, drug and branch. That destroys personal data without losing counts.
- Run the job on a schedule with a dry-run report and a signed-off count, logging who destroyed what and when.
- Restore the archive to a test environment annually to prove it is readable; an archive nobody can open is loss, not retention.
Check for longer overlapping rules: tax and medicines records may impose a longer period, and the longer one wins. If a legal hold exists (complaint or investigation), the job must skip those records.