The following doc presents the actual database en processes in the document.
Blue Boxes : Reference tables. In these tables you will find the data for lookup values, select, ...
Black Boxes : Tables with user values. All views, in the front pages use these tables. For simple tables (measurements) the tables are directly in the page. But for tables with processes, technical status,.. I used only views in the front pages. The full tables are in the Data section.
Red Box: Processes.
Tables
Appointments :
One line represents one doctor appointment. it is linked to a specific doctor and contains different details. (time, documents, ...) And a prescription of meds. There is a one to one relationship between a appointment and a prescription. A prescription can contain several meds and it is detailled in the prescription details table.
Prescription details :
Each line represent one med and details concerning the prescription for this med
start date : when the meds need to be started
morning: number of pills to take on morning.
lunch:number of pills to take at lunch time.
diner:number of pills to take at diner time.
night:number of pills to take when going to sleep.
duration: number of days the med need to be taken
appointment: link to the appointment for this prescription detail. several detail can have one appointment
Meds-Tracker
This table is populated automatically based on prescription details. The algorythm is the one described in the process paragraph.
I am using this table to track, setup reminders, and complete values.
Analysis-details
In this table all the blood analysis are stored. The category and sub-category columns are populated automatically based on the reference table based on the marker.
A conditional formatting is configured based on the blood ref table in order to highlight the user value if it is or not in a normal range for the specific marker.
Reference Tables
Time-config
The prescription details are based on morning, lunch, diner, .. in this table I am making the link between names and the time. This table is used on the process to setup the reminder and med tracker
Blood-ref
The blood ref table is the configuration of the markers with min and max values that will be used in the conditional formatting.
It is also a link with the category and sub category of the markers.
Status
I am using the status in order to see what action needs to be performed. Actions can be buttons, process,.. It is also used to disable buttons.
Process
Med Tracker : for the moment only one process is configured. When the prescription details is saved or modified the process calculate based on the start date, duration all the reminders depending on the period of the meds... For the moment if there is a change in the prescription (that should not happen in theory, because it should be another prescription) the process delete all the lines for this prescription details and create the new ones.
Template
I am using this section in order to create specific view/layout that I am using depending on where the user is in the process.
ex: when you create a appointment there is no need to see the prescription details