createTables.sql 26.05 KiB
/* updated 2014-2-1 */
CREATE TABLE users(
MID BIGINT unsigned,
Password VARCHAR(200),
Salt VARCHAR(200) DEFAULT '',
openID VARCHAR(200),
Role enum('patient','admin','hcp','uap','er','tester','pha', 'lt', 'preRegisteredPatient') NOT NULL DEFAULT 'admin',
sQuestion VARCHAR(100) DEFAULT '',
sAnswer VARCHAR(30) DEFAULT '',
isDependent tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (MID),
UNIQUE (openID)
/* Please use the MyISAM backend with no foreign keys.*/
) ENGINE=MyISAM;
CREATE TABLE hospitals(
HospitalID varchar(10),
HospitalName varchar(30) NOT NULL,
Address varchar(30),
City varchar(15),
State varchar(2),
Zip varchar(10),
PRIMARY KEY (hospitalID)
) ENGINE=MyISAM;
CREATE TABLE wards(
wardID BIGINT unsigned AUTO_INCREMENT primary key,
inHospital varchar(10) NOT NULL,
requiredSpecialty varchar(128),
FOREIGN KEY (InHospital) REFERENCES hospitals (HospitalID)
) ENGINE=MyISAM;
CREATE TABLE personnel(
MID BIGINT unsigned default NULL,
AMID BIGINT unsigned default NULL,
role enum('admin','hcp','uap','er','tester','pha', 'lt') NOT NULL default 'admin',
enabled tinyint(1) unsigned NOT NULL default '0',
lastName varchar(20) NOT NULL default '',
firstName varchar(20) NOT NULL default '',
address1 varchar(30) NOT NULL default '',
address2 varchar(30) NOT NULL default '',
city varchar(15) NOT NULL default '',
state enum('','AK','AL','AR','AZ','CA','CO','CT','DE','DC','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') NOT NULL default '',
zip varchar(10) NOT NULL default '',
phone varchar(12) NOT NULL default '',
specialty varchar(40) default NULL,
email varchar(55) default '',
PRIMARY KEY (MID)
) auto_increment=9000000000 ENGINE=MyISAM;
CREATE TABLE patients(
MID BIGINT unsigned auto_increment,
lastName varchar(20) default '',
firstName varchar(20) default '',
email varchar(55) default '',
address1 varchar(30) default '',
address2 varchar(30) default '',
city varchar(15) default '',
state enum('AK','AL','AR','AZ','CA','CO','CT','DE','DC','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') default 'AK',
zip varchar(10) default '',
phone varchar(12) default '',
eName varchar(40) default '',
ePhone varchar(12) default '',
iCName varchar(20) default '',
iCAddress1 varchar(30) default '',
iCAddress2 varchar(30) default '',
iCCity varchar(15) default '',
ICState enum('AK','AL','AR','AZ','CA','CO','CT','DE','DC','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') default 'AK', iCZip varchar(10) default '',
iCPhone varchar(12) default '',
iCID varchar(20) default '',
DateOfBirth DATE,
DateOfDeath DATE,
CauseOfDeath VARCHAR(10) default '',
MotherMID INTEGER(10) default 0,
FatherMID INTEGER(10) default 0,
BloodType VARCHAR(3) default '',
Ethnicity VARCHAR(20) default '',
Gender VARCHAR(13) default 'Not Specified',
TopicalNotes VARCHAR(200) default '',
CreditCardType VARCHAR(20) default '',
CreditCardNumber VARCHAR(19) default '',
DirectionsToHome varchar(512) default '',
Religion varchar(64) default '',
Language varchar(32) default '',
SpiritualPractices varchar(512) default '',
AlternateName varchar(32) default '',
DateOfDeactivation DATE default NULL,
PRIMARY KEY (MID)
) ENGINE=MyISAM;
CREATE TABLE historypatients(
ID BIGINT unsigned auto_increment,
changeDate DATE NOT NULL,
changeMID BIGINT unsigned NOT NULL,
MID BIGINT unsigned NOT NULL,
lastName varchar(20) default '',
firstName varchar(20) default '',
email varchar(55) default '',
address1 varchar(30) default '',
address2 varchar(30) default '',
city varchar(15) default '',
state enum('AK','AL','AR','AZ','CA','CO','CT','DE','DC','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') default 'AK',
zip varchar(10) default '',
phone varchar(12) default '',
eName varchar(40) default '',
ePhone varchar(12) default '',
iCName varchar(20) default '',
iCAddress1 varchar(30) default '',
iCAddress2 varchar(30) default '',
iCCity varchar(15) default '',
ICState enum('AK','AL','AR','AZ','CA','CO','CT','DE','DC','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY') default 'AK',
iCZip varchar(10) default '',
iCPhone varchar(12) default '',
iCID varchar(20) default '',
DateOfBirth DATE,
DateOfDeath DATE,
CauseOfDeath VARCHAR(10) default '',
MotherMID INTEGER(10) default 0,
FatherMID INTEGER(10) default 0,
BloodType VARCHAR(3) default '',
Ethnicity VARCHAR(20) default '',
Gender VARCHAR(13) default 'Not Specified',
TopicalNotes VARCHAR(200) default '',
CreditCardType VARCHAR(20) default '',
CreditCardNumber VARCHAR(19) default '',
DirectionsToHome varchar(512) default '',
Religion varchar(64) default '',
Language varchar(32) default '',
SpiritualPractices varchar(512) default '',
AlternateName varchar(32) default '',
DateOfDeactivation DATE default NULL,
PRIMARY KEY (ID)
) ENGINE=MyISAM;
CREATE TABLE ophthalmology(
MID BIGINT unsigned NOT NULL default '0',
OID BIGINT unsigned auto_increment, dateVisit DATE,
docLastName varchar(20),
docFirstName varchar(20),
vaNumOD int(4),
vaDenOD int(4),
vaNumOS int(4),
vaDenOS int(4),
sphereOD float,
sphereOS float,
cylinderOD float,
cylinderOS float,
axisOD int(4),
axisOS int(4),
addOD float,
addOS float,
PRIMARY KEY (OID)
) ENGINE=MyISAM;
CREATE TABLE ophthalmologySchedule(
PATIENTMID BIGINT unsigned NOT NULL default '0',
DOCTORMID BIGINT unsigned NOT NULL default '0',
OID BIGINT unsigned auto_increment,
dateTime DATETIME,
docLastName varchar(20),
docFirstName varchar(20),
comments mediumtext,
pending BOOLEAN NOT NULL,
accepted BOOLEAN NOT NULL,
PRIMARY KEY (OID)
) ENGINE=MyISAM;
CREATE TABLE ophthalmologySurgery(
MID BIGINT unsigned NOT NULL default '0',
OID BIGINT unsigned auto_increment,
dateVisit DATE,
docLastName varchar(20),
docFirstName varchar(20),
vaNumOD int(4),
vaDenOD int(4),
vaNumOS int(4),
vaDenOS int(4),
sphereOD float,
sphereOS float,
cylinderOD float,
cylinderOS float,
axisOD int(4),
axisOS int(4),
addOD float,
addOS float,
surgery varchar(40),
surgeryNotes varchar(400),
PRIMARY KEY (OID)
) ENGINE=MyISAM;
CREATE TABLE obstetrics(
MID BIGINT unsigned NOT NULL default '0',
OID BIGINT unsigned auto_increment,
pregId BIGINT unsigned NOT NULL default '0',
LMP DATE,
EDD DATE,
weeksPregnant varchar(4) default '',
dateVisit DATE,
yearConception int(4) default 0,
hoursInLabor float default 0,
deliveryType enum('Vaginal Delivery','Caesarean Section','Miscarriage') default 'Vaginal Delivery',
pregnancyStatus enum('Initial', 'Office Visit', 'Complete') default 'Initial',
weight float default 0,
bloodPressureS int default 0,
bloodPressureD int default 0,
FHR int default 0, FHU float default 0,
PRIMARY KEY (OID)
) ENGINE=MyISAM;
CREATE TABLE flags(
FID BIGINT unsigned auto_increment,
MID BIGINT unsigned NOT NULL default '0',
pregId BIGINT unsigned NOT NULL default '0',
flagType enum('High Blood Pressure', 'Advanced Maternal Age', 'Maternal Allergies', 'Low-Lying Placenta',
'Genetic Miscarriage', 'Abnormal FHR', 'Twins', 'Abnormal Weight Change', 'Negative Blood Type', 'Pregnancy relevant pre-existing conditions'),
PRIMARY KEY (FID)
) ENGINE=MyISAM;
CREATE TABLE loginfailures(
ipaddress varchar(128) NOT NULL,
failureCount int NOT NULL default 0,
lastFailure TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ipaddress)
) ENGINE=MyISAM;
CREATE TABLE resetpasswordfailures(
ipaddress varchar(128) NOT NULL,
failureCount int NOT NULL default 0,
lastFailure TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ipaddress)
) ENGINE=MyISAM;
CREATE TABLE icdcodes (
Code decimal(5,2) NOT NULL,
Description TEXT NOT NULL,
Chronic enum('no','yes') NOT NULL default 'no',
Ophthalmology enum('no','yes') NOT NULL default 'no',
URL varchar(512) NOT NULL default '',
PRIMARY KEY (Code)
) ENGINE=MyISAM;
CREATE TABLE cptcodes(
Code varchar(5) NOT NULL COMMENT 'Actual CPT Code',
Description varchar(30) NOT NULL COMMENT 'Description of the CPT Codes',
Attribute varchar(30),
PRIMARY KEY (Code)
) ENGINE=MyISAM;
CREATE TABLE drugreactionoverridecodes(
Code varchar(5) NOT NULL COMMENT 'Identifier for override reason',
Description varchar(80) NOT NULL COMMENT 'Description of override reason',
PRIMARY KEY (Code)
) ENGINE=MyISAM;
CREATE TABLE ndcodes(
Code varchar(10) NOT NULL,
Description varchar(100) NOT NULL,
PRIMARY KEY (Code)
) ENGINE=MyISAM;
CREATE TABLE druginteractions(
FirstDrug varchar(9) NOT NULL,
SecondDrug varchar(9) NOT NULL,
Description varchar(100) NOT NULL,
PRIMARY KEY (FirstDrug,SecondDrug)
) ENGINE=MyISAM;
CREATE TABLE transactionlog(
transactionID int(10) unsigned NOT NULL auto_increment,
loggedInMID BIGINT unsigned NOT NULL DEFAULT '0',
secondaryMID BIGINT unsigned NOT NULL DEFAULT '0',
transactionCode int(10) UNSIGNED NOT NULL default '0',
timeLogged timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
addedInfo VARCHAR(255) default '',
PRIMARY KEY (transactionID)) ENGINE=MyISAM;
CREATE TABLE hcprelations(
HCP BIGINT unsigned NOT NULL default '0',
UAP BIGINT unsigned NOT NULL default '0',
PRIMARY KEY (HCP, UAP)
) ENGINE=MyISAM;
CREATE TABLE personalrelations(
PatientID BIGINT unsigned NOT NULL COMMENT 'MID of the patient',
RelativeID BIGINT unsigned NOT NULL COMMENT 'MID of the Relative',
RelativeType VARCHAR( 35 ) NOT NULL COMMENT 'Relation Type'
) ENGINE=MyISAM;
CREATE TABLE representatives(
representerMID BIGINT unsigned default 0,
representeeMID BIGINT unsigned default 0,
PRIMARY KEY (representerMID,representeeMID)
) ENGINE=MyISAM;
CREATE TABLE hcpassignedhos(
hosID VARCHAR(10) NOT NULL,
HCPID BIGINT unsigned NOT NULL,
PRIMARY KEY (hosID,HCPID)
) ENGINE=MyISAM;
CREATE TABLE declaredhcp(
PatientID BIGINT unsigned NOT NULL default '0',
HCPID BIGINT unsigned NOT NULL default '0',
PRIMARY KEY (PatientID,HCPID)
) ENGINE=MyISAM;
CREATE TABLE officevisits(
ID int(10) unsigned auto_increment,
visitDate date default '0000-00-00',
appt_type VARCHAR(30) NOT NULL default 'General Checkup', /* UC60 */
HCPID BIGINT unsigned default '0',
notes mediumtext,
PatientID BIGINT unsigned default '0',
HospitalID VARCHAR(10) default '',
IsERIncident BOOLEAN default false,
IsBilled BOOLEAN default false, /* UC60 */
PRIMARY KEY (ID)
) ENGINE=MyISAM;
CREATE TABLE billing( /* UC60 */
billID int(10) unsigned auto_increment,
appt_id INT UNSIGNED,
PatientID BIGINT unsigned NOT NULL default '0',
HCPID BIGINT unsigned default '0',
billTimeS DATE,
amt int,
status VARCHAR(20) default '',
ccHolderName VARCHAR(30),
billingAddress VARCHAR(120),
ccType VARCHAR(20),
ccNumber VARCHAR(40),
cvv VARCHAR(4),
insHolderName VARCHAR(30),
insID VARCHAR(20),
insProviderName VARCHAR(20),
insAddress1 VARCHAR(30),
insAddress2 VARCHAR(30),
insCity VARCHAR(20),
insState enum('AK','AL','AR','AZ','CA','CO','CT','DE','DC','FL','GA','HI','IA','ID','IL','IN','KS','KY','LA','MA','MD','ME','MI','MN','MO','MS','MT','NC','ND','NE','NH','NJ','NM','NV','NY','OH','OK','OR','PA','RI','SC','SD','TN','TX','UT','VA','VT','WA','WI','WV','WY'),
insZip VARCHAR(10),
insPhone VARCHAR(12),
submissions int default 0,
subTime datetime,
isInsurance BOOLEAN default false, PRIMARY KEY (billID),
FOREIGN KEY (appt_id) REFERENCES appointment (appt_id),
FOREIGN KEY (PatientID) REFERENCES patients (MID),
FOREIGN KEY (HCPID) REFERENCES personnel (MID)
) ENGINE=MyISAM;
CREATE TABLE personalhealthinformation (
PatientID BIGINT unsigned NOT NULL default '0',
OfficeVisitID BIGINT unsigned NOT NULL default '0',
Height float default '0',
Weight float default '0',
HeadCircumference float default '0',
Smoker tinyint(1) NOT NULL default '0' COMMENT 'Is the person a smoker',
SmokingStatus int(1) NOT NULL default '9' COMMENT 'Smoking Status Code',
HouseholdSmokingStatus int(1) NOT NULL default '1' COMMENT 'Household Smoking Status Code',
BloodPressureN int(11) default '0',
BloodPressureD int(11) default '0',
CholesterolHDL int(11) default '0' COMMENT 'HDL Cholesterol',
CholesterolLDL int(11) default '0' COMMENT 'LDL Ccholesterol',
CholesterolTri int(11) default '0' COMMENT 'Cholesterol Triglyceride',
HCPID BIGINT unsigned default NULL,
AsOfDate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
OfficeVisitDate date NOT NULL default '0000-00-00',
BMI float default '-1'
/*PRIMARY KEY (OfficeVisitID)*/
) ENGINE=MyISAM;
CREATE TABLE personalallergies(
PatientID BIGINT unsigned NOT NULL COMMENT 'MID of the Patient',
Allergy VARCHAR( 50 ) NOT NULL COMMENT 'Description of the allergy'
) ENGINE=MyISAM;
CREATE TABLE allergies(
ID INT(10) unsigned auto_increment primary key,
PatientID BIGINT unsigned NOT NULL COMMENT 'MID of the Patient',
Description VARCHAR( 50 ) NOT NULL COMMENT 'Description of the allergy',
FirstFound TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Code varchar(10) COMMENT 'NDCode of drug that patient is allergic to'
/*NEW, Added Code, so that we could pass the NDCode of the drug when adding allergy.*/
) ENGINE=MyISAM;
CREATE TABLE ovprocedure(
ID INT(10) auto_increment primary key,
VisitID INT( 10 ) unsigned NOT NULL COMMENT 'ID of the Office Visit',
CPTCode VARCHAR( 5 ) NOT NULL COMMENT 'CPTCode of the procedure',
HCPID VARCHAR( 10 ) NOT NULL DEFAULT ''
) ENGINE=MyISAM;
CREATE TABLE ovmedication (
ID INT(10) auto_increment primary key,
VisitID INT( 10 ) unsigned NOT NULL COMMENT 'ID of the Office Visit',
NDCode VARCHAR( 9 ) NOT NULL COMMENT 'NDCode for the medication',
StartDate DATE,
EndDate DATE,
Dosage INT DEFAULT 0 COMMENT 'Always in mg - this could certainly be changed later',
Instructions VARCHAR(500) DEFAULT '',
OverrideOther VARCHAR(255) DEFAULT '' COMMENT 'Provided if user chooses other reason'
) ENGINE=MyISAM;
CREATE TABLE ovreactionoverride (
ID INT(10) auto_increment primary key,
OVMedicationID INT(10) NOT NULL COMMENT 'Must correspond to an ID in OVMedication table',
OverrideCode VARCHAR(5) COMMENT 'Code identifier of the override reason',
FOREIGN KEY (OVMedicationID) REFERENCES OVMedication (ID)
) ENGINE=MyISAM;
CREATE TABLE ovdiagnosis (
ID INT(10) auto_increment primary key,
VisitID INT( 10 ) unsigned NOT NULL COMMENT 'ID of the Office Visit', ICDCode DECIMAL( 5, 2 ) NOT NULL COMMENT 'Code for the Diagnosis',
URL VARCHAR(512) COMMENT 'URL for information'
) ENGINE=MyISAM;
CREATE TABLE globalvariables (
Name VARCHAR(20) primary key,
Value VARCHAR(20)
) ENGINE=MyISAM;
INSERT INTO globalvariables(Name,Value) VALUES ('Timeout', '20');
CREATE TABLE fakeemail(
ID INT(10) auto_increment primary key,
ToAddr VARCHAR(100),
FromAddr VARCHAR(100),
Subject VARCHAR(500),
Body VARCHAR(2000),
AddedDate timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM;
CREATE TABLE reportrequests (
ID INT(10) auto_increment primary key,
RequesterMID BIGINT unsigned,
PatientMID BIGINT unsigned,
ApproverMID BIGINT unsigned,
RequestedDate datetime,
ApprovedDate datetime,
ViewedDate datetime,
Status varchar(30),
Comment TEXT
) ENGINE=MyISAM;
CREATE TABLE ovsurvey (
VisitID int(10) unsigned primary key COMMENT 'ID of the Office Visit',
SurveyDate datetime not null COMMENT 'Date the survey was completed',
WaitingRoomMinutes int(3) COMMENT 'How many minutes did you wait in the waiting room?',
ExamRoomMinutes int(3) COMMENT 'How many minutes did you wait in the examination room before seeing your physician?',
VisitSatisfaction int(1) COMMENT 'How satisfied were you with your office visit?',
TreatmentSatisfaction int(1) COMMENT 'How satisfied were you with the treatment or information you received?'
) ENGINE=MyISAM;
CREATE TABLE loinc (
LaboratoryProcedureCode VARCHAR (7),
Component VARCHAR(100),
KindOfProperty VARCHAR(100),
TimeAspect VARCHAR(100),
System VARCHAR(100),
ScaleType VARCHAR(100),
MethodType VARCHAR(100)
) ENGINE=MyISAM;
CREATE TABLE labprocedure (
LaboratoryProcedureID BIGINT(10) auto_increment primary key,
PatientMID BIGINT unsigned,
LaboratoryProcedureCode VARCHAR (7),
Rights VARCHAR(10),
Status VARCHAR(20),
Commentary MEDIUMTEXT,
Results MEDIUMTEXT,
NumericalResults VARCHAR(20),
NumericalResultsUnit VARCHAR(20),
UpperBound VARCHAR(20),
LowerBound VARCHAR(20),
OfficeVisitID INT unsigned,
LabTechID LONG,
PriorityCode INT unsigned,
ViewedByPatient BOOLEAN NOT NULL default FALSE,
UpdatedDate timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM;
CREATE TABLE message (
message_id INT UNSIGNED AUTO_INCREMENT,
parent_msg_id INT UNSIGNED,
original_msg_id INT UNSIGNED,
from_id BIGINT UNSIGNED NOT NULL,
to_id BIGINT UNSIGNED NOT NULL,
sent_date DATETIME NOT NULL,
message TEXT,
subject TEXT,
been_read INT UNSIGNED,
PRIMARY KEY (message_id)
) ENGINE=MyISAM;
CREATE TABLE appointment (
appt_id INT UNSIGNED AUTO_INCREMENT primary key,
doctor_id BIGINT UNSIGNED NOT NULL,
patient_id BIGINT UNSIGNED NOT NULL,
sched_date DATETIME NOT NULL,
appt_type VARCHAR(30) NOT NULL,
comment TEXT
) ENGINE=MyISAM;
CREATE TABLE appointmenttype (
apptType_id INT UNSIGNED AUTO_INCREMENT primary key,
appt_type VARCHAR(30) NOT NULL,
duration INT UNSIGNED NOT NULL,
price INT UNSIGNED NOT NULL default '0' /* UC60 */
) ENGINE=MyISAM;
CREATE TABLE referrals (
id INT UNSIGNED AUTO_INCREMENT,
PatientID BIGINT UNSIGNED NOT NULL,
SenderID BIGINT UNSIGNED NOT NULL,
ReceiverID BIGINT UNSIGNED NOT NULL,
ReferralDetails TEXT,
OVID BIGINT UNSIGNED NOT NULL,
viewed_by_patient boolean NOT NULL,
viewed_by_HCP boolean NOT NULL,
TimeStamp DATETIME NOT NULL,
PriorityCode INT unsigned,
PRIMARY KEY (id)
) AUTO_INCREMENT=1 ENGINE=MyISAM;
CREATE TABLE remotemonitoringdata (
id INT UNSIGNED AUTO_INCREMENT,
PatientID BIGINT UNSIGNED NOT NULL,
systolicBloodPressure int(10) SIGNED default -1,
diastolicBloodPressure int(10) SIGNED default -1,
glucoseLevel int(10) SIGNED default -1,
height float default -1,
weight float default -1,
pedometerReading int(10) SIGNED default -1,
timeLogged timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
ReporterRole TEXT,
ReporterID BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) AUTO_INCREMENT=1 ENGINE=MyISAM;
CREATE TABLE remotemonitoringlists (
PatientMID BIGINT unsigned default 0,
HCPMID BIGINT unsigned default 0,
SystolicBloodPressure BOOLEAN default true,
DiastolicBloodPressure BOOLEAN default true,
GlucoseLevel BOOLEAN default true,
Height BOOLEAN default true,
Weight BOOLEAN default true,
PedometerReading BOOLEAN default true,
PRIMARY KEY (PatientMID,HCPMID)
) ENGINE=MyISAM;
CREATE TABLE adverseevents (
id INT UNSIGNED AUTO_INCREMENT primary key,
Status VARCHAR(10) default "Active",
PatientMID BIGINT unsigned default 0,
PresImmu VARCHAR(50),
Code VARCHAR(20),
Comment VARCHAR(2000),
Prescriber BIGINT unsigned default 0,
TimeLogged timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM;
CREATE TABLE profilephotos (
MID BIGINT (10) primary key,
Photo LONGBLOB,
UpdatedDate timestamp NOT NULL default CURRENT_TIMESTAMP
) ENGINE=MyISAM;
CREATE TABLE patientspecificinstructions (
id BIGINT unsigned AUTO_INCREMENT primary key,
VisitID BIGINT unsigned,
Modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
Name VARCHAR(100),
URL VARCHAR(250),
Comment VARCHAR(500)
) ENGINE=MyISAM;
CREATE TABLE referralmessage(
messageID INT unsigned NOT NULL,
referralID INT unsigned NOT NULL,
PRIMARY KEY (messageID,referralID)
) ENGINE=MyISAM;
CREATE TABLE appointmentrequests(
appt_id INT UNSIGNED AUTO_INCREMENT primary key,
doctor_id BIGINT UNSIGNED NOT NULL,
patient_id BIGINT UNSIGNED NOT NULL,
sched_date DATETIME NOT NULL,
appt_type VARCHAR(30) NOT NULL,
comment TEXT,
pending BOOLEAN NOT NULL,
accepted BOOLEAN NOT NULL
) ENGINE=MyISAM;
CREATE TABLE WardRooms(
RoomID BIGINT unsigned AUTO_INCREMENT primary key,
OccupiedBy BIGINT unsigned default NULL,
InWard BIGINT unsigned NOT NULL,
RoomName varchar(128),
Status varchar(128),
FOREIGN KEY (InWard) REFERENCES wards (WardID),
FOREIGN KEY (OccupiedBy) REFERENCES patients (MID)
) ENGINE=MyISAM;
CREATE TABLE HCPAssignedToWard(
HCP BIGINT unsigned,
WARD BIGINT unsigned,
FOREIGN KEY (WARD) REFERENCES wards (WardID),
FOREIGN KEY (HCP) REFERENCES personnel (MID)
) ENGINE=MyISAM;
CREATE TABLE WardRoomCheckout(
PID BIGINT unsigned default NULL,
Reason VARCHAR(120),
FOREIGN KEY (PID) REFERENCES patients (MID)
) ENGINE=MyISAM;
/*Table for storing weight based on sex and age */
CREATE TABLE cdcweightstats(
sex INT NOT NULL,
age FLOAT NOT NULL, L DOUBLE NOT NULL,
M DOUBLE NOT NULL,
S DOUBLE NOT NULL,
PRIMARY KEY(sex, age)
) ENGINE=innoDB;
/*Table for storing height based on sex and age */
CREATE TABLE cdcheightstats(
sex INT NOT NULL,
age FLOAT NOT NULL,
L DOUBLE NOT NULL,
M DOUBLE NOT NULL,
S DOUBLE NOT NULL,
PRIMARY KEY(sex, age)
) ENGINE=innoDB;
/*Table for storing head circumference statistics based on sex and age */
CREATE TABLE cdcheadcircumferencestats(
sex INT NOT NULL,
age FLOAT NOT NULL,
L DOUBLE NOT NULL,
M DOUBLE NOT NULL,
S DOUBLE NOT NULL,
PRIMARY KEY(sex, age)
) ENGINE=innoDB;
/*Table for storing bmi statistics based on sex and age */
CREATE TABLE cdcbmistats(
sex INT NOT NULL,
age FLOAT NOT NULL,
L DOUBLE NOT NULL,
M DOUBLE NOT NULL,
S DOUBLE NOT NULL,
PRIMARY KEY(sex, age)
) ENGINE=innoDB;
/*Table for storing z-score values and probabilities*/
CREATE TABLE normaltable(
z DOUBLE NOT NULL,
_00 DOUBLE NOT NULL,
_01 DOUBLE NOT NULL,
_02 DOUBLE NOT NULL,
_03 DOUBLE NOT NULL,
_04 DOUBLE NOT NULL,
_05 DOUBLE NOT NULL,
_06 DOUBLE NOT NULL,
_07 DOUBLE NOT NULL,
_08 DOUBLE NOT NULL,
_09 DOUBLE NOT NULL,
PRIMARY KEY(z)
) ENGINE=innoDB;
/*Table for maintaining records release information*/
CREATE TABLE recordsrelease(
releaseID BIGINT UNSIGNED AUTO_INCREMENT,
requestDate DATETIME NOT NULL,
pid BIGINT UNSIGNED NOT NULL,
releaseHospitalID VARCHAR(30) NOT NULL,
recHospitalName VARCHAR(30) NOT NULL,
recHospitalAddress VARCHAR(120) NOT NULL,
docFirstName VARCHAR(20) NOT NULL,
docLastName VARCHAR(20) NOT NULL,
docPhone VARCHAR(12) NOT NULL,
docEmail VARCHAR(100) NOT NULL,
justification VARCHAR(120),
status INT NOT NULL,
PRIMARY KEY(releaseID)
) ENGINE=innoDB;
CREATE TABLE requiredprocedures( cptCode VARCHAR(10) NOT NULL,
description VARCHAR(30) NOT NULL,
ageGroup INT NOT NULL,
attribute VARCHAR(30) NOT NULL,
ageMax BIGINT unsigned default NULL
)ENGINE=innoDB;
CREATE TABLE reviews(
mid BIGINT UNSIGNED NOT NULL,
pid BIGINT UNSIGNED NOT NULL,
reviewdate DATETIME NOT NULL,
descriptivereview VARCHAR(500),
rating INT UNSIGNED NOT NULL,
title VARCHAR(200)
)ENGINE=innoDB;
CREATE TABLE IF NOT EXISTS obstetricsconditions (
`cid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`mid` BIGINT NOT NULL,
`condition` VARCHAR(64) NOT NULL,
PRIMARY KEY (`cid`),
UNIQUE INDEX `cid_UNIQUE` (`cid` ASC)
)ENGINE=innoDB;
CREATE TABLE IF NOT EXISTS zipcodes (
`zip` varchar(5) NOT NULL default '',
`state` char(2) NOT NULL default '',
`latitude` varchar(10) NOT NULL default '',
`longitude` varchar(10) NOT NULL default '',
`city` varchar(50) default NULL,
`full_state` varchar(50) default NULL,
PRIMARY KEY `zip` (`zip`)
) ENGINE=innoDB;
CREATE TABLE IF NOT EXISTS foodEntry (
EntryID BIGINT UNSIGNED,
DateEaten DATE NOT NULL,
MealType enum('Breakfast', 'Lunch', 'Dinner', 'Snack'),
FoodName varchar(50) NOT NULL,
Servings DOUBLE UNSIGNED NOT NULL,
Calories DOUBLE UNSIGNED NOT NULL,
Fat DOUBLE UNSIGNED NOT NULL,
Sodium DOUBLE UNSIGNED NOT NULL,
Carbs DOUBLE UNSIGNED NOT NULL,
Sugar DOUBLE UNSIGNED NOT NULL,
Fiber DOUBLE UNSIGNED NOT NULL,
Protein DOUBLE UNSIGNED NOT NULL,
PatientID BIGINT UNSIGNED,
LabelID BIGINT default NULL,
PRIMARY KEY (EntryID),
FOREIGN KEY (LabelID) REFERENCES labels (EntryID)
) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS sleepEntry (
EntryID BIGINT UNSIGNED,
Date DATE NOT NULL,
SleepType enum('Nightly', 'Nap'),
Hours DOUBLE UNSIGNED NOT NULL,
PatientID BIGINT UNSIGNED,
LabelID BIGINT default NULL,
PRIMARY KEY (EntryID),
FOREIGN KEY (LabelID) REFERENCES labels (EntryID)
) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS exerciseEntry (
EntryID BIGINT UNSIGNED,
Date DATE NOT NULL,
ExerciseType enum('Cardio', 'Weight Training'),
Name varchar(50) NOT NULL,
Hours DOUBLE UNSIGNED NOT NULL, Calories INT UNSIGNED NOT NULL,
Sets INT UNSIGNED,
Reps INT UNSIGNED,
PatientID BIGINT UNSIGNED,
LabelID BIGINT default NULL,
PRIMARY KEY (EntryID),
FOREIGN KEY (LabelID) REFERENCES labels (EntryID)
) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS labels (
EntryID BIGINT UNSIGNED AUTO_INCREMENT,
PatientID BIGINT UNSIGNED,
LabelName varchar(50) NOT NULL,
LabelColor varchar(10),
PRIMARY KEY(EntryID)
) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS designatedNutritionist(
PatientID BIGINT unsigned NOT NULL default '0',
HCPID BIGINT unsigned NOT NULL default '0',
PRIMARY KEY (PatientID)
) ENGINE=MyISAM;
CREATE TABLE IF NOT EXISTS opdiagnosis(
ID INT(10) auto_increment primary key,
VisitID INT( 10 ) unsigned NOT NULL COMMENT 'ID of the Ophthalmology Visit',
ICDCode DECIMAL( 5, 2 ) NOT NULL COMMENT 'Code for the Diagnosis',
URL VARCHAR(512) COMMENT 'URL for information'
) ENGINE=MyISAM;