Core Database Classes

1. Core Database Classes

2. 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, `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. Information needed to track an individual patient’s basic data Does your application need any other information on each patient? Solution: Modify Existing Patient Table Tracking Patient Characteristics TABLE PATIENT USER_ID_PK (links to User Table) Date of Birth Sex (M/F) Height Weight

4. 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 need that is not shown? What about Prescriptions?

5. Tracking ODLs • 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 Basis • Start with ODL_Kind Table: TABLE ODL_Kind ODL_Kind_ID (Primary Key) ODL_Kind_Category (Enum: values of active or passive) ODL_Kind_Range (Enum: Scale, Values, Enumeration, etc.) ODL_Kind_Disease (Asthma, Diabetes, CHF, etc.)

6. Tracking ODLs • Now Define Different Ranges, Diseases, etc. TABLE ODL_Range_Data ODL_Range_Data_ID (Primary Key) ODL_Kind_ID (Foreign Key) ODL_Range_Data_DateTimeStamp ODL_Range_Data_Type (Enum:Fatigue, Pain, Energy Level, etc.)ODL_Range_Data_Low_Value (for Scale Range – if need low and high values) ODL_Range_Data_High_Value (for Scale Range – if need low and high values) ODL_Range_Data Values (set of one or More values for Values Range – if multiple values are listed) ODL_Range_Data_Enum_Values (set of one or more values like very good, good, bad, lousy, etc. – for Enumeration Range) Notes: For Sets – may need separate tables with IDs for actual Values. For Blue/Red/Green ODLs – only one is every active. For every ODL collected from a use, an instance/tuple of ODL_Range_Data is created. This instance will be bound to a specific user in the ODL_Patient_Data table.

7. Tracking ODLs • What about Disease? TABLE ODL_Disease_Data ODL_Disease_Data_ID (Primary Key) ODL_Kind_ID (Foreign Key) ODL_Disease_Data_DateTimeStamp ODL_Disease_Data_Type (Enum: Diabetes, Asthma, CHF, etc.) ODL_Diabetes_Data_Glucose_Level ODL_Diabetes_Data_Insulin_Taken ODL_Asthma_Data_Peak_Flow_Value ODL_Asthma_Data_Respirations ODL_Asthma_Data_Oxygen_Saturation ODL_CHF_Data_????? Notes: ODL_Disease Type may be redundant and not needed. Again, For Blue/Red/Green ODLs – only one is every active. For every ODL collected from a use, an instance/tuple of ODL_Range_Data is created. This instance will be bound to a specific user in the ODL_Patient_Data table.

8. Tracking ODLs • Now – Need to Track ODLs for actual patients TABLE ODL_Patient_Data ODL_Patient_Data_ID (Primary Key) ODL_Kind_ID (Foreign Key – for direct access to ODL_Kind) ODL_Patient_Data_ODL_ID (this references either a ODL_Range_Data instance/tuple or ODL_Disease_Data Instance/Tuple, etc. 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, maninpulate as needed.

9. Next Steps for Specific Groups • Group A • Need to Define a Physician (or Provider) table that tracks information on Providers • Use a Similar Approach to Patient Table and Reuse User Table • Patient Table: Do you need a Condition List? • Your ODLs should be Handled by Proposed ODL Tables • Need to Consider the Generation of Reports and if you need to Store these in a Table or Just Generated on Demand

10. Next Steps for Specific Groups • Group B • Your ER Diagram is too “flow-oriented” in trying to Represent System Features and Capabilities • Prescription and Google Account Entities should be Handled by User/Prescription Tables • Your ODLs should be Handled by Proposed ODL Tables • Need to Consider the Generation of Reports and if you need to Store these in a Table or Just Generated on Demand

11. Next Steps for Specific Groups • Group C • Need an Updated Specification with Respect to What your Focus is Going to be re. Reminders, Messages, etc. • Is Health meant to be ODLs? • Account Table should be Handled by User and Patient Tables • What about Physicians/Providers? Do you need these? • What Data may you Need to Add to Existing Tables?