The business problem
Pharmacies are heavily regulated. If a controlled-substance prescription changes — dosage, refills, who picked it up — there must be an immutable audit trail. Application code can't be the only line of defense (developers come and go, apps get rewritten); the database itself has to enforce auditing. This case walks through building exactly that with triggers.
It's also a great proving ground for normalization — the prescription/insurance domain has tricky cardinalities and natural denormalization temptations.
Case kit
Step-by-step trigger tutorial
- Trigger Tutorial — Prescription AuditFrom schema setup to working audit triggers.
- w6_prescription_database.sqlSQL schema for the prescription/insurance database.
Normalization context
- Prescription & Insurance — Normalization WalkthroughHow the schema arrived at this shape — read alongside the trigger tutorial.
Topics you'll be applying
- Decision 4 — Business Rules · the core technique
- Decision 3 — Database Design · normalization context