Project Scope and Explanation for Medical Clinic Database System
This system has been designed to track and maintain all patients, doctors and appointments (attended or unattended) of a small medical clinic.
There are many issues presented in this particular problem, mainly keeping track of if an appointment has been upheld or not. As it is possible for an appointment to be made by a patient, but the patient may or not show up for this appointment, a separate table was created to track just those appointments that turned into an actual patient visit.
There are also some issues when dealing with the payments of each visit. As a customer can pay via insurance or out of pocket, the method of payment is also tracked. In many cases, in particular with out of pocket payments, there will be an outstanding remaining balance tracked in each invoice.
The major assumptions made is that we have no way of knowing if an appointment will be attended, so we must keep track of both the appointments, and the actual visit. Assumptions can not be made about the payment, as we will not know if an invoice will be created for an appointment.
Depending on other business rules implemented, we may have to add attributes to keep track of a missed-appointment payment, as well as a Boolean searchable value to find paid or unpaid (although this could still be queried using the remaining balance > 0).