# MySQL-Front 5.0 (Build 1.0)
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
# Host: andlinux Database: hilis_new
# ------------------------------------------------------
# Server version 5.0.45-Debian_1ubuntu3.3-log
DROP DATABASE IF EXISTS `hilis_new`;
CREATE DATABASE `hilis_new` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `hilis_new`;
#
# Table structure for table case
#
CREATE TABLE `case` (
`patient_id` int(11) unsigned NOT NULL,
`location_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`patient_id`,`location_id`),
KEY `location` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table case_unit_number
#
CREATE TABLE `case_unit_number` (
`case_patient_id` int(11) unsigned NOT NULL,
`case_location_id` int(11) unsigned NOT NULL,
`unit_number` varchar(255) NOT NULL,
PRIMARY KEY (`case_patient_id`,`case_location_id`,`unit_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table clinician_region
#
CREATE TABLE `clinician_region` (
`region_code` varchar(3) NOT NULL,
`referrer_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`region_code`,`referrer_id`),
KEY `referrer` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table general_practice
#
CREATE TABLE `general_practice` (
`location_id` int(11) unsigned NOT NULL,
`address` text,
`postal_code` varchar(10) NOT NULL,
PRIMARY KEY (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table hospital
#
CREATE TABLE `hospital` (
`location_id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`region_code` varchar(3) NOT NULL,
PRIMARY KEY (`location_id`),
KEY `region` (`region_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table hospital_posting
#
CREATE TABLE `hospital_posting` (
`referrer_id` int(11) unsigned NOT NULL,
`hospital_id` int(11) unsigned NOT NULL,
`speciality_national_code` int(11) unsigned NOT NULL,
KEY `referrer_posting` (`referrer_id`,`hospital_id`),
KEY `speciality` (`speciality_national_code`),
KEY `hospital` (`hospital_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table location
#
CREATE TABLE `location` (
`id` int(11) unsigned NOT NULL auto_increment,
`organisation_code` char(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table patient
#
CREATE TABLE `patient` (
`id` int(11) unsigned NOT NULL auto_increment,
`last_name` varchar(25) NOT NULL,
`first_name` varchar(30) NOT NULL,
`dob` date default NULL,
`gender` enum('M','F','U') NOT NULL,
`nhs_number` varchar(10) default NULL,
`time` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table referrer
#
CREATE TABLE `referrer` (
`id` int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table referrer_posting
#
CREATE TABLE `referrer_posting` (
`referrer_id` int(11) unsigned NOT NULL default '0',
`location_id` int(11) unsigned NOT NULL default '0',
`created_at` datetime NOT NULL,
`inactivated_at` datetime default NULL,
`active` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`referrer_id`,`location_id`),
KEY `location` (`location_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table region
#
CREATE TABLE `region` (
`code` varchar(3) NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table request
#
CREATE TABLE `request` (
`id` int(11) unsigned NOT NULL auto_increment,
`created_at` datetime NOT NULL,
`year` int(5) NOT NULL,
`request_number` int(5) NOT NULL,
`case_location_id` int(11) unsigned NOT NULL,
`case_patient_id` int(11) unsigned NOT NULL,
`case_unit_number_unit_number` varchar(255) NOT NULL,
`referrer_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `lab_number` (`year`,`request_number`),
KEY `referrer_posting` (`case_location_id`,`referrer_id`),
KEY `case_unit_number` (`case_patient_id`,`case_location_id`,`case_unit_number_unit_number`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Table structure for table speciality
#
CREATE TABLE `speciality` (
`national_code` int(11) unsigned NOT NULL,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`national_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#
# Foreign keys for table case
#
ALTER TABLE `case`
ADD CONSTRAINT `case_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`id`),
ADD CONSTRAINT `case_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);
#
# Foreign keys for table case_unit_number
#
ALTER TABLE `case_unit_number`
ADD CONSTRAINT `case` FOREIGN KEY (`case_patient_id`, `case_location_id`) REFERENCES `case` (`patient_id`, `location_id`);
#
# Foreign keys for table clinician_region
#
ALTER TABLE `clinician_region`
ADD CONSTRAINT `clinician_region_ibfk_1` FOREIGN KEY (`region_code`) REFERENCES `region` (`code`),
ADD CONSTRAINT `clinician_region_ibfk_2` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`);
#
# Foreign keys for table general_practice
#
ALTER TABLE `general_practice`
ADD CONSTRAINT `general_practice_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`);
#
# Foreign keys for table hospital
#
ALTER TABLE `hospital`
ADD CONSTRAINT `hospital_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
ADD CONSTRAINT `hospital_ibfk_2` FOREIGN KEY (`region_code`) REFERENCES `region` (`code`);
#
# Foreign keys for table hospital_posting
#
ALTER TABLE `hospital_posting`
ADD CONSTRAINT `hospital_posting_ibfk_1` FOREIGN KEY (`referrer_id`, `hospital_id`) REFERENCES `referrer_posting` (`referrer_id`, `location_id`),
ADD CONSTRAINT `hospital_posting_ibfk_2` FOREIGN KEY (`speciality_national_code`) REFERENCES `speciality` (`national_code`),
ADD CONSTRAINT `hospital_posting_ibfk_3` FOREIGN KEY (`hospital_id`) REFERENCES `hospital` (`location_id`);
#
# Foreign keys for table referrer_posting
#
ALTER TABLE `referrer_posting`
ADD CONSTRAINT `location` FOREIGN KEY (`location_id`) REFERENCES `location` (`id`),
ADD CONSTRAINT `referrer` FOREIGN KEY (`referrer_id`) REFERENCES `referrer` (`id`);
#
# Foreign keys for table request
#
ALTER TABLE `request`
ADD CONSTRAINT `request_ibfk_1` FOREIGN KEY (`case_location_id`, `referrer_id`) REFERENCES `referrer_posting` (`location_id`, `referrer_id`),
ADD CONSTRAINT `request_ibfk_2` FOREIGN KEY (`case_patient_id`, `case_location_id`, `case_unit_number_unit_number`) REFERENCES `case_unit_number` (`case_patient_id`, `case_location_id`, `unit_number`);
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
