-- MySQL dump 10.16  Distrib 10.2.11-MariaDB, for Win64 (AMD64)
--
-- Host: localhost    Database: fire_20171013
-- ------------------------------------------------------
-- Server version	10.2.11-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `arzt`
--

DROP TABLE IF EXISTS `arzt`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `arzt` (
  `arztPK` bigint(20) NOT NULL,
  `arztname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`arztPK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `diagnose`
--

DROP TABLE IF EXISTS `diagnose`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `diagnose` (
  `diagPK` int(11) NOT NULL AUTO_INCREMENT,
  `konsFK` int(11) NOT NULL,
  `patFK` int(11) NOT NULL,
  `icpc` char(3) NOT NULL,
  `diagnose` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`diagPK`),
  KEY `fk_diagnose_konsultation1` (`konsFK`),
  KEY `fk_diagnose_patient1` (`patFK`),
  KEY `icpc_key` (`icpc`),
  CONSTRAINT `fk_diagnose_konsultation1` FOREIGN KEY (`konsFK`) REFERENCES `konsultation` (`konsPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_diagnose_patient1` FOREIGN KEY (`patFK`) REFERENCES `patient` (`patPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4745987 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `imports`
--

DROP TABLE IF EXISTS `imports`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `imports` (
  `importPK` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `returncode` varchar(255) NOT NULL,
  `importdate` timestamp NULL DEFAULT current_timestamp(),
  `fileMD5` char(32) CHARACTER SET latin1 DEFAULT NULL,
  `logfilename` varchar(255) DEFAULT NULL,
  `firstkons` date DEFAULT NULL COMMENT 'Datum der ersten Konsultation in der importierten Datei',
  `lastkons` date DEFAULT NULL COMMENT 'Datum der letzten Konsultation in der importierten Datei',
  `nrNewKons` int(11) DEFAULT NULL,
  `nrChangedKons` int(11) DEFAULT NULL,
  `nrErrors` int(11) DEFAULT NULL,
  `nrWarnings` int(11) DEFAULT NULL,
  PRIMARY KEY (`importPK`),
  KEY `key_filename` (`filename`),
  KEY `key_fileMD5` (`fileMD5`)
) ENGINE=InnoDB AUTO_INCREMENT=4564 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `konsultation`
--

DROP TABLE IF EXISTS `konsultation`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `konsultation` (
  `konsPK` int(11) NOT NULL AUTO_INCREMENT,
  `patFK` int(11) NOT NULL,
  `arztFK` bigint(20) NOT NULL,
  `importFK` int(11) NOT NULL,
  `konsdate` date NOT NULL,
  `ts` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`konsPK`),
  KEY `fk_konsultation_arzt` (`arztFK`),
  KEY `fk_konsultation_patient` (`patFK`),
  KEY `fk_konsultation_imports1` (`importFK`),
  KEY `index_importFK` (`importFK`),
  CONSTRAINT `fk_konsultation_arzt` FOREIGN KEY (`arztFK`) REFERENCES `arzt` (`arztPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_konsultation_imports1` FOREIGN KEY (`importFK`) REFERENCES `imports` (`importPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_konsultation_patient` FOREIGN KEY (`patFK`) REFERENCES `patient` (`patPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3731386 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `labor`
--

DROP TABLE IF EXISTS `labor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `labor` (
  `laborPK` int(11) NOT NULL AUTO_INCREMENT,
  `konsFK` int(11) NOT NULL,
  `patFK` int(11) NOT NULL,
  `analyse` varchar(50) NOT NULL,
  `orig_analyse` varchar(50) NOT NULL DEFAULT 'please initialize!',
  `laborwert` varchar(50) NOT NULL,
  `laborwertNumber` float DEFAULT NULL,
  `labordate` date DEFAULT NULL,
  `quelle` varchar(50) DEFAULT NULL,
  `einheit` varchar(50) DEFAULT NULL,
  `max1` float DEFAULT NULL,
  `min1` float DEFAULT NULL,
  `abnormalflag` varchar(50) DEFAULT NULL,
  `valid` enum('yes','no','unknown') DEFAULT 'unknown',
  PRIMARY KEY (`laborPK`),
  KEY `fk_labor_konsultation1` (`konsFK`),
  KEY `fk_labor_patient1` (`patFK`),
  KEY `analyse_key` (`analyse`),
  KEY `orig_a_key` (`orig_analyse`),
  KEY `valid_key` (`valid`),
  CONSTRAINT `fk_labor_konsultation1` FOREIGN KEY (`konsFK`) REFERENCES `konsultation` (`konsPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_labor_patient1` FOREIGN KEY (`patFK`) REFERENCES `patient` (`patPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7960932 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `medi`
--

DROP TABLE IF EXISTS `medi`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `medi` (
  `mediPK` int(11) NOT NULL AUTO_INCREMENT,
  `konsFK` int(11) NOT NULL,
  `patFK` int(11) NOT NULL,
  `pharmacode` varchar(10) NOT NULL,
  `atc` varchar(10) DEFAULT NULL,
  `dosismo` varchar(25) DEFAULT NULL,
  `dosismi` varchar(25) DEFAULT NULL,
  `dosisab` varchar(25) DEFAULT NULL,
  `dosisna` varchar(25) DEFAULT NULL,
  `stopdate` date DEFAULT NULL,
  `stopbegr` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`mediPK`),
  KEY `fk_medi_konsultation1` (`konsFK`),
  KEY `fk_medi_patient1` (`patFK`),
  KEY `key_pharmacode` (`pharmacode`),
  CONSTRAINT `fk_medi_konsultation1` FOREIGN KEY (`konsFK`) REFERENCES `konsultation` (`konsPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_medi_patient1` FOREIGN KEY (`patFK`) REFERENCES `patient` (`patPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=21585351 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `patient`
--

DROP TABLE IF EXISTS `patient`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `patient` (
  `patPK` int(11) NOT NULL AUTO_INCREMENT,
  `arztFK` bigint(20) NOT NULL,
  `patid` int(11) NOT NULL,
  `patyear` int(11) DEFAULT NULL,
  `patgender` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`patPK`),
  KEY `fk_patient_arzt1` (`arztFK`),
  CONSTRAINT `fk_patient_arzt1` FOREIGN KEY (`arztFK`) REFERENCES `arzt` (`arztPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=491927 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pharmacodes`
--

DROP TABLE IF EXISTS `pharmacodes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pharmacodes` (
  `pharmacode` varchar(10) NOT NULL,
  `atc` varchar(10) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `kategorie` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`pharmacode`),
  KEY `atc` (`atc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `rel_imports_arzt`
--

DROP TABLE IF EXISTS `rel_imports_arzt`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rel_imports_arzt` (
  `importFK` int(11) NOT NULL,
  `arztFK` bigint(20) NOT NULL,
  PRIMARY KEY (`importFK`,`arztFK`),
  KEY `fk_rel_imports_arzt_imports1` (`importFK`),
  KEY `fk_rel_imports_arzt_arzt1` (`arztFK`),
  CONSTRAINT `fk_rel_imports_arzt_arzt1` FOREIGN KEY (`arztFK`) REFERENCES `arzt` (`arztPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_imports_arzt_imports1` FOREIGN KEY (`importFK`) REFERENCES `imports` (`importPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `vital`
--

DROP TABLE IF EXISTS `vital`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vital` (
  `vitalPK` int(11) NOT NULL AUTO_INCREMENT,
  `konsFK` int(11) NOT NULL,
  `patFK` int(11) NOT NULL,
  `bdsyst` int(11) DEFAULT NULL,
  `bddiast` int(11) DEFAULT NULL,
  `puls` int(11) DEFAULT NULL,
  `gewicht` float DEFAULT NULL,
  `groesse` float DEFAULT NULL,
  `bauchumfang` int(11) DEFAULT NULL,
  `bmi` float DEFAULT NULL,
  PRIMARY KEY (`vitalPK`),
  KEY `fk_vital_patient1` (`patFK`),
  KEY `fk_vital_konsultation1` (`konsFK`),
  CONSTRAINT `fk_vital_konsultation1` FOREIGN KEY (`konsFK`) REFERENCES `konsultation` (`konsPK`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_vital_patient1` FOREIGN KEY (`patFK`) REFERENCES `patient` (`patPK`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=966726 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-12-01 14:14:19