# 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 */;
