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