Lrj2NDws

1# MySQL Setup
2SET default_storage_engine=InnoDB;
3
4USE epidemics;
5
6DROP TABLE IF EXISTS person_type;
7
8CREATE TABLE person_type (
9`id` INT NOT NULL PRIMARY KEY,
10`person_type_name` VARCHAR(100) NOT NULL
11)ENGINE=InnoDB;
12
13DROP TABLE IF EXISTS person;
14
15CREATE TABLE person (
16`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
17`username` VARCHAR(25) NOT NULL,
18`password` VARCHAR(50),
19`active` BIT(1),
20`person_type_id` INT NOT NULL,
21`firstName` VARCHAR(50) NOT NULL,
22`middleName` VARCHAR(50) NULL,
23`lastName` VARCHAR(50) NOT NULL,
24`contactPhone` VARCHAR(50) NULL,
25`contactEmail` VARCHAR(50) NULL,
26`contactFax` VARCHAR(50) NULL,
27`addressLine1` VARCHAR(100) NULL,
28`addressLine2` VARCHAR(100) NULL,
29`city` VARCHAR(50) NULL,
30`state` VARCHAR(50) NULL,
31`zip` VARCHAR(10) NULL,
32FOREIGN KEY (person_type_id)
33      REFERENCES person_type(id)
34      ON DELETE CASCADE
35)ENGINE=InnoDB;
36
37DROP TABLE IF EXISTS questions;
38
39CREATE TABLE questions (
40`id` INT NOT NULL PRIMARY KEY,
41`question_text` VARCHAR(2000) NOT NULL
42)ENGINE=InnoDB;
43
44DROP TABLE IF EXISTS age_group;
45
46CREATE TABLE age_group (
47`id` INT NOT NULL PRIMARY KEY,
48`age_group_desc` VARCHAR(100) NOT NULL
49)ENGINE=InnoDB;
50
51
52DROP TABLE IF EXISTS questionnaire;
53
54CREATE TABLE questionnaire (
55`id` INT NOT NULL PRIMARY KEY,
56`question_id` INT NOT NULL,
57`age_group_id` INT NOT NULL,
58FOREIGN KEY (question_id)
59    REFERENCES questions(id)
60    ON DELETE CASCADE,
61FOREIGN KEY (age_group_id)
62    REFERENCES age_group(id)
63    ON DELETE CASCADE
64)ENGINE=InnoDB;
65
66DROP TABLE IF EXISTS answerChoice;
67
68CREATE TABLE answerChoice (
69`id` INT NOT NULL PRIMARY KEY,
70`question_id` INT NOT NULL,
71`answer_desc` VARCHAR(1000) NOT NULL,
72FOREIGN KEY (question_id)
73    REFERENCES questions(id)
74    ON DELETE CASCADE
75)ENGINE=InnoDB;
76
77DROP TABLE IF EXISTS visit;
78-- Here we are assuming that patient is not a doctor. Need to fix this deficinecy somepoint soon
79CREATE TABLE visit (
80`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
81`patient_id` INT NOT NULL,
82`doctor_id` INT NOT NULL,
83`visitDate` DATETIME NOT NULL,
84FOREIGN KEY (patient_id)
85    REFERENCES person(id)
86    ON DELETE CASCADE,
87FOREIGN KEY (doctor_id)
88    REFERENCES person(id)
89    ON DELETE CASCADE
90)ENGINE=InnoDB;
91
92DROP TABLE IF EXISTS response;
93
94CREATE TABLE response (
95`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
96`visit_id` INT NOT NULL,
97`questionnaire_id` INT NOT NULL,
98`question_id` INT NOT NULL,
99`answer_choice` INT NOT NULL,
100FOREIGN KEY (visit_id)
101	REFERENCES visit(id)
102    ON DELETE CASCADE,
103FOREIGN KEY (questionnaire_id)
104    REFERENCES questionnaire(id)
105    ON DELETE CASCADE,
106FOREIGN KEY (question_id)
107    REFERENCES questions(id)
108    ON DELETE CASCADE,
109FOREIGN KEY (answer_choice)
110    REFERENCES answerChoice(id)
111    ON DELETE CASCADE
112)ENGINE=InnoDB;