01_schema_aarsh.sql

# MySQL Setup
SET default_storage_engine=InnoDB;

USE epidemics;

DROP TABLE IF EXISTS person_type;

CREATE TABLE person_type (
`id` INT NOT NULL PRIMARY KEY,
`person_type_name` VARCHAR(100) NOT NULL
)ENGINE=InnoDB;

DROP TABLE IF EXISTS person;

CREATE TABLE person (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(25) NOT NULL,
`password` VARCHAR(50),
`active` BIT(1),
`person_type_id` INT NOT NULL,
`firstName` VARCHAR(50) NOT NULL,
`middleName` VARCHAR(50) NULL,
`lastName` VARCHAR(50) NOT NULL,
`contactPhone` VARCHAR(50) NULL,
`contactEmail` VARCHAR(50) NULL,
`contactFax` VARCHAR(50) NULL,
`addressLine1` VARCHAR(100) NULL,
`addressLine2` VARCHAR(100) NULL,
`city` VARCHAR(50) NULL,
`state` VARCHAR(50) NULL,
`zip` VARCHAR(10) NULL,
FOREIGN KEY (person_type_id)
      REFERENCES person_type(id)
      ON DELETE CASCADE
)ENGINE=InnoDB;

DROP TABLE IF EXISTS questions;

CREATE TABLE questions (
`id` INT NOT NULL PRIMARY KEY,
`question_text` VARCHAR(2000) NOT NULL
)ENGINE=InnoDB;

DROP TABLE IF EXISTS age_group;

CREATE TABLE age_group (
`id` INT NOT NULL PRIMARY KEY,
`age_group_desc` VARCHAR(100) NOT NULL
)ENGINE=InnoDB;


DROP TABLE IF EXISTS questionnaire;

CREATE TABLE questionnaire (
`id` INT NOT NULL PRIMARY KEY,
`question_id` INT NOT NULL,
`age_group_id` INT NOT NULL,
FOREIGN KEY (question_id)
    REFERENCES questions(id)
    ON DELETE CASCADE,
FOREIGN KEY (age_group_id)
    REFERENCES age_group(id)
    ON DELETE CASCADE
)ENGINE=InnoDB;

DROP TABLE IF EXISTS answerChoice;

CREATE TABLE answerChoice (
`id` INT NOT NULL PRIMARY KEY,
`question_id` INT NOT NULL,
`answer_desc` VARCHAR(1000) NOT NULL,
FOREIGN KEY (question_id)
    REFERENCES questions(id)
    ON DELETE CASCADE
)ENGINE=InnoDB;

DROP TABLE IF EXISTS visit;
-- Here we are assuming that patient is not a doctor. Need to fix this deficinecy somepoint soon
CREATE TABLE visit (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`patient_id` INT NOT NULL,
`doctor_id` INT NOT NULL,
`visitDate` DATETIME NOT NULL,
FOREIGN KEY (patient_id)
    REFERENCES person(id)
    ON DELETE CASCADE,
FOREIGN KEY (doctor_id)
    REFERENCES person(id)
    ON DELETE CASCADE
)ENGINE=InnoDB;

DROP TABLE IF EXISTS response;

CREATE TABLE response (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`visit_id` INT NOT NULL,
`questionnaire_id` INT NOT NULL,
`question_id` INT NOT NULL,
`answer_choice` INT NOT NULL,
FOREIGN KEY (visit_id)
	REFERENCES visit(id)
    ON DELETE CASCADE,
FOREIGN KEY (questionnaire_id)
    REFERENCES questionnaire(id)
    ON DELETE CASCADE,
FOREIGN KEY (question_id)
    REFERENCES questions(id)
    ON DELETE CASCADE,
FOREIGN KEY (answer_choice)
    REFERENCES answerChoice(id)
    ON DELETE CASCADE
)ENGINE=InnoDB;