Moving a Three-Branch Pharmacy from Spreadsheets to a Database
Greenleaf Pharmacy runs three branches in Douala and employs 12 staff. Each branch keeps its own Excel workbook listing about 4,000 products with columns for code, description, batch, expiry date, buying price, selling price and quantity. The workbooks are emailed to the owner each Friday. Last month a cashier accidentally sorted only one column and corrupted prices; two branches ran out of a fast-moving antimalarial while a third held 600 expired tablets that were still shown as available. Staff also create duplicate product codes when a supplier changes packaging. The owner wants one central database accessible from all branches, with the pharmacist able to update batch and expiry data, cashiers able to record sales but not change prices, and the manager able to see daily stock and expiry reports. Two staff have basic Excel skills; none has used a database. The branches have ADSL connections and one shared laptop each. Advise the owner on the move from spreadsheets to a database.
1. Explain, using examples from the pharmacy, why the current spreadsheet arrangement causes the errors described. Identify the data integrity problems that a relational database must solve.
Read the worked answer
Why the spreadsheet fails:
- Single flat sheet: product, batch, price and quantity are mixed in one row, so the same product is repeated for each batch. A supplier packaging change tempts staff to create a new code, producing duplicates.
- Sorting one column: Excel sorts only the selected range unless the whole table is selected; the cashier's action moved prices relative to products. A database applies changes to whole records and enforces relationships, so this corruption cannot happen in the same way.
- No validation: free-text codes and prices allow typos and invalid dates. There is no uniqueness constraint on product code.
- No transaction log: the price overwrite cannot be traced to a user.
- No shared concurrency control: emailed copies create divergent versions; stock is not updated at the point of sale, so the 600 expired tablets remain 'available' and two branches stock out.
Integrity problems to solve: entity integrity (unique product and batch identifiers), referential integrity (a sale must refer to an existing product and batch), domain integrity (valid prices, dates and quantities), and user-defined rules (expiry date must be after manufacture; selling price must not be below buying price without authorisation).
2. Propose a normalized set of tables for the central system, naming primary and foreign keys, and explain how the design prevents duplicate product codes and allows batch/expiry tracking.
Read the worked answer
Proposed tables (third normal form):
- Product (
ProductIDPK, Description, Category, SupplierID FK, SellingPrice, ReorderLevel) - Supplier (
SupplierIDPK, Name, Contact) - Batch (
BatchIDPK, ProductID FK, BatchNumber, ExpiryDate, QuantityOnHand, BuyingPrice) - Sale (
SaleIDPK, SaleDate, StaffID FK, BranchID FK, Total) - SaleItem (
SaleIDFK,BatchIDFK, Quantity, UnitPrice) with composite PK (SaleID,BatchID) - Staff (
StaffIDPK, Name, Role, BranchID)
How it prevents the problems: ProductID is generated by the system and unique; a supplier's new packaging is recorded as a new batch under the same product, not a new product. A sale line must reference an existing BatchID, so stock is deducted from the correct batch. Expiry is a batch attribute, so a query can list batches expiring within 90 days and block sale of expired stock. Prices live in one place, and a price change is an update to Product, not a column sort. Foreign keys stop sales that refer to non-existent products or batches.
Judgement: keep batch separate from product because buying price and expiry differ per batch, while selling price is normally per product.
3. Recommend validation rules and access controls for the three staff roles, and outline a realistic migration and training plan.
Read the worked answer
Validation: ProductID auto-generated; Description compulsory; SellingPrice at least 0 and not below BuyingPrice without manager approval; ExpiryDate later than today for saleable stock; QuantityOnHand an integer no less than 0; BatchNumber unique within a product; lookup lists for Category and Supplier; input masks for dates. Enforce referential integrity and a rule that a sale quantity cannot exceed the batch quantity.
Access control:
- Cashier: create sales, view product, price and available stock; cannot change prices, add products or delete records.
- Pharmacist: add or edit batches, expiry dates and stock adjustments; cannot change selling prices or see full financial reports.
- Manager: full reporting, price changes and user administration; all changes logged. Use individual logins, least privilege and an audit table for price and stock changes. Disable shared accounts.
Migration and training: clean and deduplicate the Excel files first; agree a coding standard; import products, then batches; reconcile opening stock and expiry dates; run the database and spreadsheets in parallel for one month at one branch; train role by role with a short manual and a super-user per branch; back up daily and test a restore before go-live. Judgement: do not attempt a big-bang cutover with staff who have never used a database.