1 / 12

Core Database Classes

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,

nalani
Download Presentation

Core Database Classes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Basi TABLE ODL_Kind ODL_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 Patients 1 ODL_Kind has one Category, One Range, and One Disease 1 Patient uses Multiple ODL_Kinds

  6. Type Tables • These Three Tables Keep the Values of the ODL • Category – Active/Passive • Range – Scale, Values, etc. • Disease – Asthma, Diabetes, Arthritis, etc. TABLE Category_Type Category_Type_ID_PK Value (Integer: 0, 1, 2, etc.) Type (String: Active, Passive values) ID_PK Value Type 12134 0 Active 12131 1 Passive TABLE Range_Type Range_Type_ID_PK Value (Integer: 0, 1, 2, etc.) Type (String: Scale, Values, Enumeration, etc.) TABLE Disease_Type Disease_Type_ID_PK Value (Integer: 0, 1, 2, etc.) Type (String: Asthma, Diabetes, CHF, etc.)

  7. Tracking ODLs • Now Define Different Ranges, Diseases, etc. TABLE ODL_Range_Values ODL_Range_Value_ID_PK (Primary Key) ODL_Kind_ID_FK (Foreign Key) DateTimeStamp Active_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 bound to a specific user in the ODL_Patient_Data table.

  8. Tables of Range and Enumeration Values • These Three Tables Keep the Values of the ODL • Category – Active/Passive • Range – Scale, Values, etc. • Disease – Asthma, Diabetes, Arthritis, etc. TABLE Range_Vals Range_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 TABLE Enum_Vals Emum_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. Tracking ODLs • What about Disease? TABLE ODL_Disease_Values ODL_Disease_Values_ID_PK (Primary Key) ODL_Kind_ID_FK (Foreign Key) DateTimeStamp Active_Values (String: Diabetes – blue, Asthma – red,CHF– Green) Glucose_Level – integer? Insulin_Taken– integer? Peak_Flow_Value – integer Respirations - integer Oxygen_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. Tracking ODLs • Now – Need to Track ODLs for actual patients TABLE 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. Alarms/Reminder • Alarm/Reminder and Schedules TABLE AlarmRemind AlarmRemind_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 Schedule Schedule_ID_PK Day_of_Week (Part of PK, Enum: Sun to Sat) Times_Per_Day (integer) TABLE Sound_Type Sound_Type_ID_PK Value (Integer: 0, 1, 2, etc.) Type (String: Vibrate, Ring)

  12. Provider Table • Providers Have: • Provider_ID_PK • User_ID_FK • Current Provider Table in Design Shown Below • Group A Do you need anything else?

More Related