core database classes
DESCRIPTION
Core Database Classes. The User Table. CREATE TABLE `tmr`.`user` ( `USER_ID_PK` int(11) NOT NULL AUTO_INCREMENT, `UserName` varchar(25) NOT NULL, `Password` varchar(25) NOT NULL, `RecoveryAnswer` varchar(100) NOT NULL, `CompleteName` varchar(255) NOT NULL, - PowerPoint PPT PresentationTRANSCRIPT
1
CSE4904
Core Database ClassesCore Database Classes
2
CSE4904
The User TableThe User TableCREATE TABLE `tmr`.`user` ( `USER_ID_PK` int(11) NOT NULL AUTO_INCREMENT, `UserName` varchar(25) NOT NULL, `Password` varchar(25) NOT NULL, `RecoveryAnswer` varchar(100) NOT NULL, `CompleteName` varchar(255) NOT NULL, `NamePrefix` varchar(10) DEFAULT NULL, `FirstName` varchar(50) NOT NULL, `LastName` varchar(50) NOT NULL, `NameSuffix` varchar(10) DEFAULT NULL, `MiddleName` varchar(25) DEFAULT NULL, `UpdateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `UpdateDescription` varchar(255) DEFAULT NULL, `InsertDate` datetime NOT NULL, `RECOVERYQUESTION_ID_FK` int(11) NOT NULL, `PRIMARYEMAIL_ID_FK` int(11) NOT NULL, `THEME_ID_FK` int(11) NOT NULL, `TYPE_ID_FK` int(11) NOT NULL, `Gender` varchar(25) DEFAULT NULL, `INCOMELEVEL_ID_FK` int(11) DEFAULT NULL, `RACE_ID_FK` int(11) DEFAULT NULL, `TECHNOLOGY_ID_FK` int(11) DEFAULT NULL, `DOB` datetime DEFAULT NULL, `GoogleAccount` varchar(45) DEFAULT NULL, `GooglePassword` varchar(25) DEFAULT NULL, PRIMARY KEY (`USER_ID_PK`),KEY `FK_USER_THEME` (`THEME_ID_FK`), KEY `FK_USER_TYPE` (`TYPE_ID_FK`), KEY `FK_USER_EMAIL` (`PRIMARYEMAIL_ID_FK`), KEY `FK_INCOMELEVEL` (`INCOMELEVEL_ID_FK`),Etc.
3
CSE4904
Tracking Patient CharacteristicsTracking Patient Characteristics
Information needed to track an individual patient’s Information needed to track an individual patient’s basic databasic data
Does your application need any other information Does your application need any other information on each patient?on each patient?
Solution: Modify Existing Patient TableSolution: Modify Existing Patient Table
TABLE PATIENTUSER_ID_PK (links to User Table)Date of BirthSex (M/F)HeightWeight
4
CSE4904
What about Prescriptions?What about Prescriptions? Two alternatives for storage:Two alternatives for storage:
Google Health (XML Instance) has this information
MySQL DB has apatient_medication Table which can be modified
Again – what else do you Again – what else do you need that is not shown?need that is not shown?
5
CSE4904
Tracking ODLsTracking ODLs Need to Distinguish Between:Need to Distinguish Between:
Storing Information on each of the Different Kinds of ODLs (Passive, Active, etc.)
Storing Actual Values (Instances) for Each User on a Day-by-Day Basi
TABLE ODL_KindODL_Kind_ID_PK (Primary Key)
Category_FK (References Category_Type Table) (Enum: values of active or passive)
Range_FK (References Range_Type Table) (Enum: Scale, Values, Enumeration, etc.)
Disease_FK (References Disease_Type Table (Enum: Asthma, Diabetes, CHF, etc.)
Notes:1 ODL_Kind Used by Multiple Patients1 ODL_Kind has one Category, One Range, and One Disease1 Patient uses Multiple ODL_Kinds
6
CSE4904
Type TablesType Tables
TABLE Category_TypeCategory_Type_ID_PKValue (Integer: 0, 1, 2, etc.)Type (String: Active, Passive values)
TABLE Range_TypeRange_Type_ID_PKValue (Integer: 0, 1, 2, etc.)Type (String: Scale, Values, Enumeration, etc.)
TABLE Disease_TypeDisease_Type_ID_PKValue (Integer: 0, 1, 2, etc.)Type (String: Asthma, Diabetes, CHF, etc.)
These Three Tables Keep the Values of the ODLThese Three Tables Keep the Values of the ODL Category – Active/Passive Range – Scale, Values, etc. Disease – Asthma, Diabetes, Arthritis, etc.
ID_PK Value Type12134 0 Active12131 1 Passive
7
CSE4904
Tracking ODLsTracking ODLs Now Define Different Ranges, Diseases, etc.Now Define Different Ranges, Diseases, etc.
TABLE ODL_Range_ValuesODL_Range_Value_ID_PK (Primary Key)ODL_Kind_ID_FK (Foreign Key)DateTimeStampActive_Values (String: Scale – blue, Values – red, Enum – Green)Low_Value (for Scale Range – if need low and high values)High_Value (for Scale Range – if need low and high values)Increment (for Scale Range – what is the increment on scale)Values_ID_FK (Range_Vals Table: references a set of one
or More values for Values Range)Enum_ID_FK (Enum_Vals Table: references a set of one
or more values like very good, good, bad, lousy, etc.)SEE NEXT SLIDE FOR Range_Vals and Enum_Vals Tables
Notes: For Blue/Red/Green ODLs – only one is every active (Active_Values). For every ODL collected from a use, an instance/tuple of
ODL_Range_Values is created. This instance will be boundto a specific user in the ODL_Patient_Data table.
8
CSE4904
Tables of Range and Enumeration ValuesTables of Range and Enumeration Values
TABLE Range_ValsRange_Values_ID_PK (links to Values_ID_FK in
ODL_Range_Values Table)Value (Integer: 1, 3, 4, … etc.)Note: Combination of Range_Values_ID_PK + Value defines
all of the values for the values and uniquely ids each one
These Three Tables Keep the Values of the ODLThese Three Tables Keep the Values of the ODL Category – Active/Passive Range – Scale, Values, etc. Disease – Asthma, Diabetes, Arthritis, etc.
TABLE Enum_ValsEmum_Values_ID_PK (links to Enum_ID_FK in
ODL_Range_Values Table)Value (String – Good, VeryGood, etc.)Note: Combination of Enum_Values_ID_PK + Value defines
all of the values for the values, and uniquely ids each one
9
CSE4904
Tracking ODLsTracking ODLs What about Disease?What about Disease?TABLE ODL_Disease_Values
ODL_Disease_Values_ID_PK (Primary Key)ODL_Kind_ID_FK (Foreign Key)DateTimeStampActive_Values (String: Diabetes – blue, Asthma – red, CHF– Green)Glucose_Level – integer?Insulin_Taken– integer?Peak_Flow_Value – integerRespirations - integerOxygen_Saturation – percent Blood_Pressure_Systolic –integer Blood_Pressure_Diastolic –integer Pulse –integer Respirations –integer
Notes: Again, For Blue/Red/Green ODLs – only one is every active. For every ODL collected from a use, an instance/tuple of
ODL_Disease_Values is created. This instance will be bound to a specific user in the ODL_Patient_Data table.
10
CSE4904
Tracking ODLsTracking ODLs Now – Need to Track ODLs for actual patientsNow – Need to Track ODLs for actual patientsTABLE ODL_Patient_Data
ODL_Patient_Data_ID_PK (Primary Key)Kind_ID_FK (Foreign Key – for direct access to ODL_Kind)Patient_ID_FK (References Patient Table)Range_Type_ID_FK (References Range_Type Table)Disease_Type_ID_FK (References Disease_Type Table)
Notes: The ODL_Patient_Data_ODL_ID references data collected by each patient. You simply pull back all data (regardless of timestamp) to get all of the ODLs and then sort, manipulate as needed.
11
CSE4904
Alarms/ReminderAlarms/Reminder Alarm/Reminder and SchedulesAlarm/Reminder and Schedules
TABLE AlarmRemindAlarmRemind_ID (Primary Key)
Patient_ID_FK (references Patient Table)Schedule_ID_FK (referneces Schedule Table)Medication_ID_FK (References PatientMed)Sound_ID (References Sound_Type table)Message (String message re. Alarm)Start_Date (Date Time Stamp for Alarm) End_Date (Date Time Stamp for Alarm)
TABLE ScheduleSchedule_ID_PKDay_of_Week (Part of PK, Enum: Sun to Sat)Times_Per_Day (integer)
TABLE Sound_TypeSound_Type_ID_PKValue (Integer: 0, 1, 2, etc.)Type (String: Vibrate, Ring)
12
CSE4904
Provider TableProvider Table Providers Have:Providers Have:
Provider_ID_PK User_ID_FK Current Provider Table in Design Shown Below Group A Do you need anything else?