# MySQL-Front 5.1  (Build 3.0)

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='STRICT_ALL_TABLES' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;


# Host: andlinux    Database: dako_refactor
# ------------------------------------------------------
# Server version 5.0.45-Debian_1ubuntu3.4

#
# Source for table hospital_departments
#

CREATE TABLE `hospital_departments` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `national_code` varchar(255) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

#
# Dumping data for table hospital_departments
#

INSERT INTO `hospital_departments` VALUES (1,'600','General practice');
INSERT INTO `hospital_departments` VALUES (2,'813','Haematology');
INSERT INTO `hospital_departments` VALUES (3,'100','Surgery');

#
# Source for table parent_organisations
#

CREATE TABLE `parent_organisations` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `parent_code` varchar(6) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `description` (`description`),
  UNIQUE KEY `parent_code` (`parent_code`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

#
# Dumping data for table parent_organisations
#

INSERT INTO `parent_organisations` VALUES (1,'RR8','Leeds');
INSERT INTO `parent_organisations` VALUES (2,'RAE','Bradford');
INSERT INTO `parent_organisations` VALUES (3,'B12345','Tinshill Surgery');
INSERT INTO `parent_organisations` VALUES (4,'B24680','Another Surgery');

#
# Source for table patient_case
#

CREATE TABLE `patient_case` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `patient_id` int(11) unsigned NOT NULL default '0',
  `referral_source_id` int(10) unsigned NOT NULL default '0',
  `unit_number` varchar(255) NOT NULL default 'UNKNOWN',
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `case` (`patient_id`,`unit_number`,`referral_source_id`),
  KEY `patient_id` (`patient_id`),
  KEY `referral_source_id` (`referral_source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table patient_case
#

INSERT INTO `patient_case` VALUES (1,1,1,'2020','2009-06-13 09:46:12');

#
# Source for table patients
#

CREATE TABLE `patients` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `last_name` varchar(50) default NULL,
  `first_name` varchar(50) default NULL,
  `middle_name` varchar(50) default NULL,
  `dob` date default NULL,
  `gender` enum('M','F','U') default 'U',
  `nhs_number` varchar(10) default NULL,
  `created_at` timestamp NOT NULL default '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `nhs_number` (`nhs_number`),
  KEY `name` (`last_name`,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table patients
#

INSERT INTO `patients` VALUES (1,'green','steven',NULL,'1955-02-06','M','1111111111','0000-00-00 00:00:00','2009-06-13 09:45:55');

#
# Source for table referral_sources
#

CREATE TABLE `referral_sources` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `display_name` varchar(255) NOT NULL default '',
  `organisation_code` varchar(6) NOT NULL default '',
  `parent_organisation_id` int(11) unsigned NOT NULL default '0',
  `referral_type_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `organisation_code` (`organisation_code`),
  KEY `referral_type_id` (`referral_type_id`),
  KEY `parent_organisation_id` (`parent_organisation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

#
# Dumping data for table referral_sources
#

INSERT INTO `referral_sources` VALUES (1,'LGI','RR801',1,3);
INSERT INTO `referral_sources` VALUES (2,'Tinshill Surgery, Tinshill Lane, etc','B12345',3,4);
INSERT INTO `referral_sources` VALUES (3,'SJUH','RR802',1,3);
INSERT INTO `referral_sources` VALUES (4,'Leeds Nuffield','NT225',1,3);
INSERT INTO `referral_sources` VALUES (5,'BRI','RAE01',2,3);

#
# Source for table referral_types
#

CREATE TABLE `referral_types` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `description` varchar(255) NOT NULL default '',
  `default_unknown` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `description` (`description`(10))
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

#
# Dumping data for table referral_types
#

INSERT INTO `referral_types` VALUES (1,'clinician','C9999998');
INSERT INTO `referral_types` VALUES (2,'practitioner','G9999998');
INSERT INTO `referral_types` VALUES (3,'hospital','X99999');
INSERT INTO `referral_types` VALUES (4,'practice','V81999');

#
# Source for table referrer_department_map
#

CREATE TABLE `referrer_department_map` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `referrer_id` int(11) unsigned NOT NULL default '0',
  `parent_organisation_id` int(11) unsigned NOT NULL default '0',
  `hospital_department_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `referrer_parent_organisation` (`referrer_id`,`parent_organisation_id`),
  KEY `hospital_department_id` (`hospital_department_id`),
  KEY `parent_organisation_id` (`parent_organisation_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

#
# Dumping data for table referrer_department_map
#

INSERT INTO `referrer_department_map` VALUES (1,1,1,2);
INSERT INTO `referrer_department_map` VALUES (2,1,2,3);
INSERT INTO `referrer_department_map` VALUES (3,2,3,1);
INSERT INTO `referrer_department_map` VALUES (4,3,1,3);
INSERT INTO `referrer_department_map` VALUES (5,4,3,1);

#
# Source for table referrers
#

CREATE TABLE `referrers` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `national_code` varchar(8) NOT NULL default '',
  `referral_type_id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `national_code` (`national_code`),
  KEY `referral_type_id` (`referral_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

#
# Dumping data for table referrers
#

INSERT INTO `referrers` VALUES (1,'Brown, CC','C1234567',1);
INSERT INTO `referrers` VALUES (2,'Green, DE','G7654321',2);
INSERT INTO `referrers` VALUES (3,'Black, TF','C2468100',1);
INSERT INTO `referrers` VALUES (4,'Silver, KI','G1357901',2);

#
# Source for table requests
#

CREATE TABLE `requests` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `request_number` int(5) unsigned default NULL,
  `year` int(5) unsigned default NULL,
  `patient_case_id` int(11) unsigned default NULL,
  `referrer_id` int(11) unsigned NOT NULL default '0',
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `lab_no` (`request_number`,`year`),
  KEY `patient_case_id` (`patient_case_id`),
  KEY `referrer_id` (`referrer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#
# Dumping data for table requests
#

INSERT INTO `requests` VALUES (1,1,2009,1,1,'2009-06-13 09:46:34');

#
#  Foreign keys for table patient_case
#

ALTER TABLE `patient_case`
ADD CONSTRAINT `patient_case_ibfk_2` FOREIGN KEY (`referral_source_id`) REFERENCES `referral_sources` (`id`),
ADD CONSTRAINT `patient_case_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`);

#
#  Foreign keys for table referral_sources
#

ALTER TABLE `referral_sources`
ADD CONSTRAINT `referral_sources_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`),
ADD CONSTRAINT `referral_sources_ibfk_2` FOREIGN KEY (`parent_organisation_id`) REFERENCES `parent_organisations` (`id`);

#
#  Foreign keys for table referrer_department_map
#

ALTER TABLE `referrer_department_map`
ADD CONSTRAINT `referrer_department_map_ibfk_1` FOREIGN KEY (`referrer_id`) REFERENCES `referrers` (`id`),
ADD CONSTRAINT `referrer_department_map_ibfk_2` FOREIGN KEY (`parent_organisation_id`) REFERENCES `parent_organisations` (`id`),
ADD CONSTRAINT `referrer_department_map_ibfk_3` FOREIGN KEY (`hospital_department_id`) REFERENCES `hospital_departments` (`id`);

#
#  Foreign keys for table referrers
#

ALTER TABLE `referrers`
ADD CONSTRAINT `referrers_ibfk_1` FOREIGN KEY (`referral_type_id`) REFERENCES `referral_types` (`id`);

#
#  Foreign keys for table requests
#

ALTER TABLE `requests`
ADD CONSTRAINT `requests_ibfk_2` FOREIGN KEY (`patient_case_id`) REFERENCES `patient_case` (`id`),
ADD CONSTRAINT `requests_ibfk_1` FOREIGN KEY (`referrer_id`) REFERENCES `referrers` (`id`);


/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;