Moss Pharmacy in Caistor manages the medicine for most of the inhabitants of the town. It operates a small shop selling necessities and also runs a prescription service with the medical clinic. Medicine arrives according to demand through large lorries from pharmaceutical companies. It is a vital location in the small town, as Caistor is fairly remote.
Currently it interacts with the local medical clinic, so that it can obtain information about which prescription drugs are needed and who to give them to. At the moment, when the doctor prescribes medication, he gives the patient a handwritten note, or ‘prescription’ with a watermark. The patient (or a carer on behalf of the patient) then hands the note to the pharmacy dispenser, who gives them their medication if it is in stock.
It is surprising, however, how many people there are who will try and take medication that isn’t theirs. Because everyone knows one another in the town, the dispenser must currently use their judgment as to whether or not the person hasn’t simply found the prescription or forged their own. This will inevitably be a problem, as staff are replaced with less experienced dispensers and if people try and claim medication that shouldn’t belong to them. Consequently, the client would like a system implemented in which customer’s identities can be verified to a better standard.
In the past there have been legal problems concerning the giving of medicine to people who have forgotten their prescriptions. Much as alcohol cannot be supplied to people below a certain age, the pharmacy is legally bound to not give prescriptions to patients without the proof of a doctor’s consent. As some patients are known to lose or forget their receipt and can create a dilemma and often means that they have to go without their drugs unless they visit the medical clinic again. For that reason the client would like the system to keep tab of a prescription that can be created by the doctor at the medical clinic and seen by the employees in the pharmacy in case a customer doesn’t have proof on paper.
There are also great precautions that need to be taken with the data-protection act. The doctor’s computer can have any information that it needs about the patient, but they are bound by law not to send this data on to the pharmacy via prescription or otherwise unless it is a desperate emergency. Therefore when the system is automated, it has to make sure that only the allowed fields are printed on a prescription or sent over automatically to be accessed by the pharmacy employees.
If a patient has a prescription for a certain kind of medicine they expect that the pharmacy will have that in stock. However this isn’t always the case and the pharmacy has no way of seeing if there are more prescriptions for certain drugs than there are drugs in stock.
As well as the prescription service, there are several aisles of items in the shop for anyone to come in and buy, such as shampoo and aspirin. The amount of stock is currently ordered and filed on pen and paper, with sales records in large cabinets, which are cleared out monthly when it has been a year since the purchases. To know how much is in stock at the moment; somebody working at the chemist must count the stock on the shelves in their head and keep a tally every couple of days. This works sufficiently enough at the moment, as the amount of -customers and stock is low, but there has recently been a large housing estate built in the town, which means that more customers are likely to use the store, and the current system doesn’t have much room for any aggressive expansion. The client would as a result like to ensure that their new system can adapt to large amounts of customers and stock in the future.
Also the re-stocking of medicine is not very effective, as the stock is only checked every other day, and is often subject to human error in counting. Medicine that is needed by many people quickly, such as painkillers for arthritis is often diminished in stock very fast, and the fact that stock isn’t checked every day doesn’t give a good indication of whether they do need to order new products. The process is also time consuming, as the pharmacy holds The pharmacy therefore requires a way to keep track of stock as it comes in and out at regular intervals.
Summary of sub-problems:
* System for stock levels doesn’t allow for much expansion.
* System for stock levels can be subject to human error, and is time consuming.
* The filing cabinets take up a lot of space, they are barely used, and it is hard to find anything specific inside it, as well as people’s records having to be manually sorted alphabetically. Many Doctor’s handwriting cannot always be read very easily, and information written on records/prescriptions is often not very concise.
* Customers can easily cheat the system and claim medicine that isn’t theirs.
* Prescriptions are often lost by patients and medication cannot legally be given without.
* Pharmacy needs to be able to find out how many prescriptions are floating around so they can keep stocks at a better level more effectively.
Data Flow Diagram
The doctor listens to the patient’s complaint, and supplies them with a prescription via a computer. The patient then hands the prescription to somebody working at the pharmacy, who checks to see if the item is in stock. If the item is in stock, the employee hands the medicine over to the patient along with a receipt as proof of purchase. If the item isn’t in stock, however; the pharmacy employee sends an order to the pharmaceutical company who supplies the pharmacy with medicine. The company sends (via a courier) the requested drugs and an invoice containing information about the order. No response is required to the invoice, nor is any payment to be sent directly to the pharmaceutical company, as they as the supplier have all the details of the pharmacy needed to take payment.
Links between the System
1. The patient complains to the doctor, and information necessary to the pharmacy is printed on a prescription. Every prescription contains a patient’s name, therefore these two areas are linked.
2. The prescription is handed in by the patient to the pharmacy, where the member of staff will ‘verify’ that person and give them the medicine if it’s in stock. Prescriptions and Medicine are linked because on each prescription is the name of at least one kind of medication that the pharmacy must give to the patient.
3. Patients and illnesses are linked because somebody wouldn’t be a patient if they didn’t have more than one illness or condition.
4. Illnesses and Medicine are linked because for each illness currently on file there is at least one kind of recommended medication.
5. If the item isn’t in stock, then an order is made for that item. So these two are linked because when there is a lack of medicine, there is an order, and there can’t be an order without a lack of medicine.
Inputs and Outputs
1. The complaint could be anything that is wrong with the patient, from a stuffed nose to a
terrible disease and is given through conversation.
2. The prescription contains fields containing the Patient ID, Patient’s Name, Patient’s Postcode, Patient’s House Number, Patient’s Phone Number, Medication ID, Medication, and Additional Comments. The last field is a formality for if the doctor needs to specify any precautions to be taken with the patient in case they are allergic to certain medication or have an excessively serious condition. This is also used when somebody has to collect medicine on behalf of the patient.
3. Prescription is as above and is handed in to pharmacist, who confirms the customer’s identity and visits the store room to find the medication.
4. The label for the medicine is printed out with the fields for Patient ID, Patient Name, Medication, Amount in Bottle, Date Prescribed and Medication Instructions.
5. In the present system, because the stock system is recorded on paper, employees
keeping track of stocks have to use educated judgment as to whether or not stock of a certain item is low.
6. As the pharmaceutical company already has its own database system with information and addresses of pharmacies, the only fields needed on the order form is the Unique Pharmacy ID (so they know who to send the drugs to and who to charge for them), Type Of Medication Requested, Amount Requested, and the Date.
7. The invoice arrives from the pharmaceutical company and basically contains all of the information of the order. It contains the Unique Pharmacy ID, Type Of Medication Requested, Amount Requested, Total Cost, and the Date.
8. In the current system the stock numbers on the invoices are added to the current amounts of that item in stock to keep a running tally in one of the cabinets. In the other cabinet, the invoices themselves are stored (this cabinet is cleared once a year to save space.)
Ways of Tackling the Problem
With spreadsheet software I could total up amounts of stock automatically within a table using formulae within cells, and set up Conditional Formatting to warn the user if an item is close to or out of stock. I could also use an ‘IF’ function to see whether the amount of medication in stock is greater than the amount of pending receipts for that item. As a substitute for the filing cabinets in the store room, spreadsheets can hold very large amounts of data, but if many of them had to be made to accommodate the entire system then there is no real way of connecting them together to keep things in order. I could also use the ‘SORT’ function in spreadsheet software to list records more concisely and effectively.
With a database I could integrate a menu system to be used in either or both the Doctor’s office or on the pharmacy computer, which may prove easier to use than a complex spreadsheet and is fully customizable. Because I can use relationships between tables, I could implement a system which directly connects the Doctor’s computer with the Pharmacy’s. To get around the problem of people forgetting their prescriptions, information for which patient needs which medication and when can be exchanged between the tables using relationships, eliminating any real need for physical prescriptions. In addition to that, huge amounts of records can be kept and stemming from one place, which can be sorted just as in spreadsheet software.
Automatic forms could be made for printing out orders for more stock when the stocks are too low, as well as for printing out prescriptions if need be. Queries can be extremely useful for finding specific records, as well as the usefulness of update and delete queries for renewing information and removing redundant data. Even look-up tables can be used in certain fields for when the doctor is entering patient’s information into the database. There is also a way of protecting patient’s details only accessible to their doctor by passwords.
At present, every kind of ailment on file has at least one kind of prescription drug associated with it, but in the future as new discoveries are made it is likely that some illnesses if only temporarily will not have a treatment related to them. The database needs to be able to adapt to accommodate and function properly with illnesses without a linking drug. The cost of medicines will inevitably rise and fall, but since the pharmaceutical company operates on a separate system, and the pharmacy’s funding is from a government-based company; this does not directly concern this database.
In the future, the user may wish to integrate the few non-prescription items into the system as well as the prescription drugs. I need to make sure that the system is capable of keeping track of products that do not require a prescription. In the unlikely event of the medicine supplier closing down or changing, I need to make sure it is not difficult to change any details of the supplier for future reference so that the pharmacy will send orders to the correct address. Increasing the number of types of medicine in stock should be no problem, and since these additions to the database will probably happen frequently, I must ensure that entering them is easy to do.
End User Requirements
* The system must not have a need for physical filing cabinets, which occupy space. The information within the system must be sorted well, and any particular records should be single out and find.
* Customers must not be able to cheat the system and claim medicine that isn’t theirs.
* The system must supply a backup for prescriptions in case they’re lost by patients.
* The pharmacy needs to be able to find out how many prescriptions are floating around so they can keep stocks at a better level more effectively.
* The system must be computer based, so as to eliminate the problem of doctor’s bad handwriting not being able to be read.
* System for stock levels must allow for more expansion.
* System for stock levels must not be subject to human error, and not too time consuming.
* Personal patient information must be protected.
* Must be able to hold data far into the foreseeable future and not take up too much space.
* Specific data on patients and medicines must be easy to find.
* The system must look at least presentable visually.
* Must be fairly easy to use and get used to for new employees and current ones.
* Every output must be perfectly accurate, as if wrong information or medication is given to patients, it could be a disaster.
* System must be able to verify patient within 30 seconds.
* It should not take more than 30 seconds to check whether an item is in stock.
* Nobody except the doctors should be able to view personal customer information.
* The system must be able to cater for any amount of patients.
* The system must be able to accommodate any amount of types of medication.
* When medication is purchased or received, the amount of stock for that medication must decrease or increase accordingly.
* System must print two labels on one A4 page.