Design of Equipment Loaning System
There are several types of equipment, ranging from wheelchairs to neck collars, and each type of equipment is available in different sizes, there are also several of each type and size of equipment.
When deciding how to input and store the equipment into my database, I decided that their was two potential paths that I could follow: to place the equipment into my database with one record for each type and size of equipment, and having a “number in stock” field to represent multiples of equipment types with the same size. The second way of representing the data would be to let each individual piece of equipment have its own record.
I elected to take the second route, although it would take more space, it would mean that if an item was damaged in someway, a note of the problem could be made in a “notes” field in the items record.
The second problem I discovered was coming up with a way of having a code for each individual piece of equipment. Again, I found two main ways of doing this: An auto number system, or a system which would allow a user to determine what the equipment is just by looking at the code. I felt that the second way would be a better way of doing this, so I devised a system that fulfils this requirement (shown below).
Item Codes
To get the individual items code, you simply add a number onto the end of the code. E.g. Junior Wheelchair number 1 would be WC-J1.Using this system, new pieces of equipment can be added with ease, whereas with an auto number system, it would mess up the order of the numbers.
Equipment Booking Form Design
Security
This database holds confidential information about patients, and should therefore be kept secure from illegitimate access, both physically and electronically. As stated in the data protection act, it would be the hospitals responsibility to keep the data secure. I have employed a basic password protection system into the database, with only one user, as I feel that a multi user password system with different levels of access for each user would be inappropriate in this instance because the only users would be the desk staff, and occasionally the administrator for backup purposes.
To provide security on an electronic level, I would recommend making sure there is an appropriate firewall system installed if the terminal the database is running on is connected to any network external to the hospital. I would also recommend a weekly backup to a removable media, kept in a fireproof safe, with back copies kept for as long as possible, in case the system is infected with an undetected Trojan. On a physical level, I would recommend that rooms where terminals have access to the database are kept locked.
Implementation
Patient Table
Equipment Table
Equipment Status Table
Patient Titles Table
Entity Relationship Diagram
Equipment Report
Customer Letter Report
Equipment Form
Patient Form
Main Menu Form
Main Menu (Vb Code)
For the main menu, I decided to incorporate a clock, as it provides a purpose (to tell the time), and also looks good on the form.
Equipment Form (Vb Code)
For the equipment booking form, I used two visual basic sub forms; the first sets the return date and the out date when the “loan out” button is pressed, and the second sets the return date and the out date to zero when the combo box which controls the status of the item is changed. I used this last bit of code because it saves the user having to delete the date manually when the equipment is returned from loan, but it has the downside of having to set the status before pressing the “loan out” button, otherwise it undoes this action.
Autoexec Macro
To add a professional finish to my database, I made a macro that opened the main menu form automatically on open. This requires no Vb coding, just a macro called Autoexec.
