-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: classroom.cs.unc.edu
-- Generation Time: May 03, 2007 at 03:20 PM
-- Server version: 5.0.27
-- PHP Version: 4.3.9
-- 
-- Database: `accesstestdb`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `AntibioticType`
-- 

CREATE TABLE `AntibioticType` (
  `AntibioticID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number for the antibiotics',
  `FullName` varchar(128) NOT NULL COMMENT 'Full name of the antibiotic',
  `Abbrev` varchar(64) NOT NULL COMMENT 'Abbreviation of the Antibiotic as it is on the sheet',
  PRIMARY KEY  (`AntibioticID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Serves as a Lookup table for ResultsFinal contains Antibioti' AUTO_INCREMENT=11 ;

-- 
-- Dumping data for table `AntibioticType`
-- 

INSERT INTO `AntibioticType` (`AntibioticID`, `FullName`, `Abbrev`) VALUES 
(1, 'Beta Lactomase', 'B-Lac'),
(2, 'Penicillian', 'PEN'),
(3, 'Ampicillian', 'AMP'),
(4, 'Augmentin', 'AMC'),
(5, 'Clindamycin', 'CC'),
(6, 'Tetracycline', 'TE'),
(7, 'Cephalothin', 'CF'),
(8, 'Erythromycin', 'ERY'),
(9, 'Metronidazole', 'MET'),
(10, 'Other', '');

-- --------------------------------------------------------

-- 
-- Table structure for table `BacterialCount`
-- 

CREATE TABLE `BacterialCount` (
  `BCountID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number',
  `Min` int(8) unsigned NOT NULL COMMENT 'Minimum in Range Value of Bacterial Count',
  `Max` int(8) unsigned NOT NULL COMMENT 'Maximum in Range Value of Bacterial Count',
  `Description` text NOT NULL COMMENT 'Description of what the count indicates',
  PRIMARY KEY  (`BCountID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Serves as a Lookup table to DUWLInfo' AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `BacterialCount`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `CarriesResults`
-- 

CREATE TABLE `CarriesResults` (
  `SampleID` varchar(10) NOT NULL COMMENT 'Lookup to SampleInfo.SampleID',
  `FlowRate` decimal(4,1) NOT NULL COMMENT 'This is a decimal number accurate to 1 place',
  `BufferingCap` decimal(4,1) NOT NULL COMMENT 'This is a decimal field accurate to 1 place',
  `StrepMutans` float NOT NULL COMMENT 'Float Field, that will understand scientific notation as input ex: 1.23 E + 9',
  `Lacto` float NOT NULL COMMENT 'Float Field, that will understand scientific notation as input ex: 1.23 E + 9 where E is the sql word for exponent',
  `Other` float NOT NULL COMMENT 'Any Other bacteria found and its CFU/ml count',
  PRIMARY KEY  (`SampleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Results information from Carries Activity tests';

-- 
-- Dumping data for table `CarriesResults`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `ClinicInfo`
-- 

CREATE TABLE `ClinicInfo` (
  `ClinicID` int(8) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID number for clinics',
  `ClinicName` varchar(128) NOT NULL COMMENT 'Name of the clinic',
  `Address1` varchar(128) NOT NULL COMMENT 'Address Line 1 of clinic',
  `Address2` varchar(128) NOT NULL COMMENT 'Address Line 2 of clinic',
  `City` varchar(128) NOT NULL COMMENT 'City the Clinic is located in',
  `State` varchar(2) NOT NULL COMMENT 'State Abbreviation of Clinic',
  `Zip` varchar(5) NOT NULL COMMENT '5 digit zip code of the clinic',
  `Phone` varchar(16) NOT NULL COMMENT 'Clinician Phone Number',
  PRIMARY KEY  (`ClinicID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Clinic Info Table: Stores information about the clinic' AUTO_INCREMENT=29 ;

-- 
-- Dumping data for table `ClinicInfo`
-- 

INSERT INTO `ClinicInfo` (`ClinicID`, `ClinicName`, `Address1`, `Address2`, `City`, `State`, `Zip`, `Phone`) VALUES 
(22, 'Clinic 1', '12 Main St.', '', 'Chapel Hill', 'NC', '27514', '919-483-9486'),
(23, 'CleanTeeth', 'address1', '', 'chapel hill', 'nc', '27514', '4892374309'),
(24, 'NCBots', '111 Here Rd.', '', 'Greensboro', 'NC', '27546', '9194838273'),
(25, 'Whatever', 'Stuff', '', 'More', 'ST', '34544', '919847383'),
(27, 'Dentists R Us', '999 Morrow Dr.', '', 'test', 'te', 'test', 'test'),
(28, 'Are', 'We', '', 'Good', 'NC', '12345', '9194568392');

-- --------------------------------------------------------

-- 
-- Table structure for table `ClinicianInfo`
-- 

CREATE TABLE `ClinicianInfo` (
  `ClinicianID` int(8) unsigned NOT NULL auto_increment COMMENT 'Primary Key: Auto Gen ID for clinicians',
  `FirstName` varchar(128) NOT NULL COMMENT 'First Name of clinician',
  `LastName` varchar(128) NOT NULL COMMENT 'Last name of clinician',
  `Email` varchar(128) NOT NULL COMMENT 'Email address of the clinician',
  `ClinicID` int(8) unsigned NOT NULL COMMENT 'Clinic Location that the clinician is operating out of',
  PRIMARY KEY  (`ClinicianID`),
  KEY `FirstName` (`FirstName`,`LastName`),
  KEY `ClinicID` (`ClinicID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Information about clinicians' AUTO_INCREMENT=14 ;

-- 
-- Dumping data for table `ClinicianInfo`
-- 

INSERT INTO `ClinicianInfo` (`ClinicianID`, `FirstName`, `LastName`, `Email`, `ClinicID`) VALUES 
(10, 'Peyton', 'Manning', 'tdcolts@email', 22),
(11, 'Ari', 'Gold', 'agent4u@email', 23),
(12, 'Fred', 'Flinstone', 'Spun@email.unc.edu', 25);

-- --------------------------------------------------------

-- 
-- Table structure for table `CultureType`
-- 

CREATE TABLE `CultureType` (
  `CulTypeID` int(8) unsigned NOT NULL auto_increment COMMENT 'Auto Incremented ID number',
  `CulType` varchar(16) NOT NULL COMMENT 'provide values for CulType Field',
  `Cost` decimal(4,2) unsigned NOT NULL default '0.00' COMMENT 'Cost of the Kit',
  PRIMARY KEY  (`CulTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lookup table for Culture Type' AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `CultureType`
-- 

INSERT INTO `CultureType` (`CulTypeID`, `CulType`, `Cost`) VALUES 
(1, 'Surgical', '0.00'),
(2, 'Perio', '0.00'),
(3, 'Caries Activity ', '0.00'),
(4, 'DUWL', '0.00');

-- --------------------------------------------------------

-- 
-- Table structure for table `DUWL_Info`
-- 

CREATE TABLE `DUWL_Info` (
  `KitID` int(8) unsigned NOT NULL COMMENT 'Lookup to KitInfo.KitID',
  `SampleID` varchar(10) NOT NULL COMMENT 'Sample # generated using first two digits of the year, then Random for the rest',
  `RecievedDate` date default NULL COMMENT 'Date Sample was Received default to curdate()',
  `Operatory` varchar(16) NOT NULL COMMENT 'Operatory Room sample was taken from',
  `DUWLSource` varchar(128) NOT NULL COMMENT 'The Chair type in the operatory',
  `CleaningProduct` enum('Commercial','Bleach','Filter') NOT NULL COMMENT 'Possible entry is limited to: commercial, Bleach, Filter',
  `BCountID` int(11) NOT NULL,
  PRIMARY KEY  (`SampleID`),
  KEY `KitID` (`KitID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contains all information about DUWL processing';

-- 
-- Dumping data for table `DUWL_Info`
-- 

INSERT INTO `DUWL_Info` (`KitID`, `SampleID`, `RecievedDate`, `Operatory`, `DUWLSource`, `CleaningProduct`, `BCountID`) VALUES 
(237, '0748216554', '2007-05-03', 'operatory', 'source', 'Commercial', 100),
(238, '0748216575', '2007-05-03', 'op', 'sour', 'Filter', 300);

-- --------------------------------------------------------

-- 
-- Table structure for table `GrowthType`
-- 

CREATE TABLE `GrowthType` (
  `GrowthID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number for GrowthType',
  `FullName` varchar(128) NOT NULL COMMENT 'Full Name of the Growth',
  `Abbrev` varchar(3) NOT NULL COMMENT 'Abbreviation of the Growth',
  PRIMARY KEY  (`GrowthID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Serves as a Lookup table for ResultsFinal contains Growth In' AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `GrowthType`
-- 

INSERT INTO `GrowthType` (`GrowthID`, `FullName`, `Abbrev`) VALUES 
(1, 'Not Isolated', 'NI'),
(2, 'Light Growth', 'L'),
(3, 'Moderate Growth', 'M'),
(4, 'Heavy Growth', 'H');

-- --------------------------------------------------------

-- 
-- Table structure for table `KitInfo`
-- 

CREATE TABLE `KitInfo` (
  `KitID` int(8) unsigned NOT NULL auto_increment COMMENT 'Auto Generated ID field',
  `CulTypeID` int(8) unsigned NOT NULL COMMENT 'Lookup to Culture Type table (foreign key)',
  `ExpirationDate` date default NULL COMMENT 'Date kit expires follows the format yyyy-mm-dd',
  `BillDate` date default NULL COMMENT 'Date Clinic was billed for the Kit stored as yyyy-mm-dd',
  `Cost` decimal(4,2) unsigned default '0.00' COMMENT 'Cost of the Kit this will carry from CultureType.cost but may be changed',
  `ClinicID` int(8) unsigned NOT NULL COMMENT 'Name of the clinic that the kit was sent to (Lookup from ClinicInfo)',
  `ClinicianID` int(8) unsigned NOT NULL COMMENT 'Name of the clinician that the kit was sent to (Lookup from ClinicianInfo)',
  `Received` enum('T','F') NOT NULL default 'F' COMMENT 'Track Kits',
  PRIMARY KEY  (`KitID`),
  KEY `CulType` (`CulTypeID`),
  KEY `ClinicID` (`ClinicID`),
  KEY `ClinicianID` (`ClinicianID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Initial Information about the Kits' AUTO_INCREMENT=258 ;

-- 
-- Dumping data for table `KitInfo`
-- 

INSERT INTO `KitInfo` (`KitID`, `CulTypeID`, `ExpirationDate`, `BillDate`, `Cost`, `ClinicID`, `ClinicianID`, `Received`) VALUES 
(217, 1, '2007-08-08', NULL, '0.00', 25, 12, 'T'),
(218, 1, '2007-08-08', NULL, '0.00', 22, 10, 'T'),
(219, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(220, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(221, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(222, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(223, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(224, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(225, 1, '2007-06-06', NULL, '0.00', 22, 10, 'F'),
(226, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(227, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(228, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(229, 1, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(230, 3, '2007-08-08', NULL, '0.00', 22, 10, 'T'),
(231, 3, '2007-08-08', NULL, '0.00', 22, 10, 'T'),
(232, 3, '2007-09-09', NULL, '0.00', 22, 10, 'F'),
(233, 3, '2008-09-09', NULL, '0.00', 22, 10, 'F'),
(234, 3, '2008-09-09', NULL, '0.00', 22, 10, 'F'),
(235, 3, '2010-05-05', NULL, '0.00', 22, 10, 'F'),
(236, 3, '2010-05-06', NULL, '0.00', 22, 10, 'F'),
(237, 4, '2008-04-04', NULL, '0.00', 22, 10, 'T'),
(238, 4, '2008-08-08', NULL, '0.00', 22, 10, 'T'),
(239, 4, '2008-08-08', NULL, '0.00', 22, 10, 'F'),
(240, 4, '2008-08-08', NULL, '0.00', 22, 10, 'F'),
(241, 4, '2008-08-08', NULL, '0.00', 22, 10, 'F'),
(242, 4, '2008-08-08', NULL, '0.00', 22, 10, 'F'),
(243, 4, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(244, 4, '2007-09-08', NULL, '0.00', 22, 10, 'F'),
(245, 4, '2007-08-08', NULL, '0.00', 22, 10, 'F'),
(246, 4, '2008-07-07', NULL, '0.00', 22, 10, 'F'),
(247, 1, '2007-04-30', NULL, '0.00', 22, 10, 'F'),
(248, 1, '2008-04-22', NULL, '0.00', 22, 10, 'F'),
(249, 3, '2007-05-03', NULL, '0.00', 22, 10, 'F'),
(250, 1, NULL, NULL, '0.00', 25, 12, 'F'),
(251, 1, NULL, NULL, '0.00', 25, 12, 'F'),
(252, 2, NULL, NULL, '0.00', 25, 12, 'F'),
(253, 2, NULL, NULL, '0.00', 25, 12, 'F'),
(254, 3, NULL, NULL, '0.00', 25, 12, 'F'),
(255, 3, NULL, NULL, '0.00', 25, 12, 'F'),
(256, 4, NULL, NULL, '0.00', 25, 12, 'F'),
(257, 4, NULL, NULL, '0.00', 25, 12, 'F');

-- --------------------------------------------------------

-- 
-- Table structure for table `OrganismType`
-- 

CREATE TABLE `OrganismType` (
  `OrgID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number of the Organism',
  `Organism` varchar(128) NOT NULL COMMENT 'The name of the Organism',
  `Classification` enum('Aerobic','Anaerobic') NOT NULL COMMENT 'Classification data limited to Aerobic and Anaerobic',
  PRIMARY KEY  (`OrgID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Serves as a Lookup table for ResultsFinal' AUTO_INCREMENT=25 ;

-- 
-- Dumping data for table `OrganismType`
-- 

INSERT INTO `OrganismType` (`OrgID`, `Organism`, `Classification`) VALUES 
(1, 'Pseudomonas aeruginosa', 'Aerobic'),
(2, 'Enterobacteriaceae', 'Aerobic'),
(3, 'Enterococcus faecalis', 'Aerobic'),
(4, 'Staphylococcus aureus', 'Aerobic'),
(5, 'Coagulase negative beta-hemolytic staphylococci', 'Aerobic'),
(6, 'Streptococci pyogenes', 'Aerobic'),
(7, 'Beta hemolytic streptococci not group A', 'Aerobic'),
(8, 'Candida albicans', 'Aerobic'),
(9, 'Non-albicans candida', 'Aerobic'),
(10, 'Porphyromonas gingivalis', 'Anaerobic'),
(11, 'Tannerella forsythensis', 'Anaerobic'),
(12, 'Treponema denticola', 'Anaerobic'),
(13, 'Fusobacterium nucleatum', 'Anaerobic'),
(14, 'Prevotella intermedia', 'Anaerobic'),
(15, 'Campylobacter rectus', 'Anaerobic'),
(16, 'Campylobacter gracilis', 'Anaerobic'),
(17, 'Peptostreptococcus micros', 'Anaerobic'),
(18, 'Peptostreptococcus anaerobius', 'Anaerobic'),
(19, 'Streptococcus constellatus', 'Anaerobic'),
(20, 'Bacteriodes fragilis', 'Anaerobic'),
(21, 'Eikenella corrodens', 'Anaerobic'),
(22, 'Actinobacillus actinomycetemcomitans', 'Anaerobic'),
(23, 'Other', 'Aerobic'),
(24, 'Other', 'Anaerobic');

-- --------------------------------------------------------

-- 
-- Table structure for table `PatientBillInfo`
-- 

CREATE TABLE `PatientBillInfo` (
  `PatientID` int(8) unsigned NOT NULL COMMENT 'Lookup from PatientStdInfo.PatientID to obtain',
  `Address1` varchar(128) NOT NULL COMMENT 'Address Line 1 of patient',
  `Address2` varchar(128) NOT NULL COMMENT 'Line 2 of patient address',
  `City` varchar(128) NOT NULL COMMENT 'Patient City',
  `State` varchar(2) NOT NULL COMMENT 'State Abbreviation of patient',
  `Zip` varchar(5) NOT NULL COMMENT '5 digit zip code of the patient',
  PRIMARY KEY  (`PatientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='More Info about Patients, not every patient will have';

-- 
-- Dumping data for table `PatientBillInfo`
-- 

INSERT INTO `PatientBillInfo` (`PatientID`, `Address1`, `Address2`, `City`, `State`, `Zip`) VALUES 
(16, '432 Church St.', '', 'Fairview', 'AL', '38422'),
(17, '1234 Here Rd.', '', 'Where', 'NC', '12345');

-- --------------------------------------------------------

-- 
-- Table structure for table `PatientStdInfo`
-- 

CREATE TABLE `PatientStdInfo` (
  `PatientID` int(8) unsigned NOT NULL auto_increment COMMENT 'Auto Gen PatientID number',
  `FirstName` varchar(128) NOT NULL COMMENT 'Patient FirstName',
  `LastName` varchar(128) NOT NULL COMMENT 'Patient Last Name',
  `DOB` date default NULL COMMENT 'Patient Date of Birth',
  `Gender` enum('M','F') NOT NULL COMMENT 'Patient Gender, must be entered either as ''M'' or ''F''',
  `ChartNum` varchar(32) NOT NULL COMMENT 'Patient Chart number',
  PRIMARY KEY  (`PatientID`),
  KEY `name` (`FirstName`,`LastName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Standard Patient Information: Every Patient will have this i' AUTO_INCREMENT=19 ;

-- 
-- Dumping data for table `PatientStdInfo`
-- 

INSERT INTO `PatientStdInfo` (`PatientID`, `FirstName`, `LastName`, `DOB`, `Gender`, `ChartNum`) VALUES 
(13, 'John', 'Doe', '1973-06-21', 'M', ''),
(14, 'jessica', 'johnson', '1980-04-06', 'F', ''),
(15, 'John', 'Jones', '1982-12-11', 'M', '123'),
(16, '50', 'Cent', '1980-04-11', 'M', '437892'),
(17, 'Barney', 'Rubble', '1993-12-11', 'M', ''),
(18, 'david', 'michaelson', '1980-04-02', 'M', '48329034');

-- --------------------------------------------------------

-- 
-- Table structure for table `PermissionType`
-- 

CREATE TABLE `PermissionType` (
  `PermissionID` tinyint(2) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID number',
  `Permission` varchar(128) NOT NULL COMMENT 'Permission Role',
  PRIMARY KEY  (`PermissionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Lookup table for Users contains Permission Roles' AUTO_INCREMENT=4 ;

-- 
-- Dumping data for table `PermissionType`
-- 

INSERT INTO `PermissionType` (`PermissionID`, `Permission`) VALUES 
(1, 'Administrator'),
(2, 'Technician'),
(3, 'Student');

-- --------------------------------------------------------

-- 
-- Table structure for table `RejectionInfo`
-- 

CREATE TABLE `RejectionInfo` (
  `RejectID` int(8) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number ',
  `SampleID` varchar(10) NOT NULL COMMENT 'Lookup to SampleInfo.SampleID',
  `TechComments` text NOT NULL COMMENT 'Technicians comments about why the sample was rejected',
  `RCodeID` tinyint(3) unsigned NOT NULL COMMENT 'Lookup to RejectionInfo.RCodeID',
  `InformDate` date default NULL COMMENT 'Date Clinician was informed of rejection',
  PRIMARY KEY  (`RejectID`),
  KEY `SampleID` (`SampleID`),
  KEY `RCodeID` (`RCodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Information about rejected Samples' AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `RejectionInfo`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `RejectionType`
-- 

CREATE TABLE `RejectionType` (
  `RCodeID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number',
  `RejectionCode` varchar(128) NOT NULL COMMENT 'Rejection Codes for the Samples',
  PRIMARY KEY  (`RCodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This is a lookup tables that will supply values for RectionC' AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `RejectionType`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `ResultsFinal`
-- 

CREATE TABLE `ResultsFinal` (
  `ResultsID` int(8) unsigned NOT NULL COMMENT 'Lookup to ResultsInitial.ResultsID',
  `OrgID` tinyint(3) unsigned NOT NULL COMMENT 'Lookup to OrganismType.OrgID',
  `GrowthID` tinyint(3) unsigned NOT NULL COMMENT 'Lookup to GrowthType.GrowthID',
  PRIMARY KEY  (`ResultsID`,`OrgID`),
  KEY `OrgID` (`OrgID`),
  KEY `GrowthID` (`GrowthID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='All results will have this data present';

-- 
-- Dumping data for table `ResultsFinal`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `ResultsFinalMore`
-- 

CREATE TABLE `ResultsFinalMore` (
  `ResultsID` int(8) unsigned NOT NULL COMMENT 'Lookup to ResultsFinal.ResultsID',
  `AntibioticID` tinyint(3) unsigned NOT NULL COMMENT 'Lookup to AntibioticType.AntibioticID',
  `SusceptID` tinyint(3) unsigned NOT NULL COMMENT 'Lookup to SusceptibilityType.SusceptID',
  `OrgID` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`ResultsID`,`AntibioticID`,`OrgID`),
  KEY `AntibioticID` (`AntibioticID`),
  KEY `SusceptID` (`SusceptID`),
  KEY `OrgID` (`OrgID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contains more result data that not every organism will have';

-- 
-- Dumping data for table `ResultsFinalMore`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `ResultsInitial`
-- 

CREATE TABLE `ResultsInitial` (
  `ResultsID` int(8) unsigned NOT NULL auto_increment COMMENT 'AutoGen ID Number for all Results',
  `SampleID` varchar(10) NOT NULL COMMENT 'Lookup to SampleInfo.SampleID',
  `Cost` decimal(4,2) NOT NULL COMMENT 'Cost of the Result Tests',
  `BillDate` date default NULL COMMENT 'The date of bill for this result was exported',
  `TechInitials` varchar(3) default NULL COMMENT 'Max 3 char initials of technologist',
  `DirectSmear` varchar(255) NOT NULL COMMENT 'DirectSmear data on the sample ',
  `ProcessDate` date default NULL COMMENT 'Date the result was processed default',
  `SmearDate` date default NULL COMMENT 'Date Direct Smear was taken, as this differs from the result processed date',
  PRIMARY KEY  (`ResultsID`),
  KEY `SampleID` (`SampleID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Initial Standard Information about all results except DUWL s' AUTO_INCREMENT=10 ;

-- 
-- Dumping data for table `ResultsInitial`
-- 


-- --------------------------------------------------------

-- 
-- Table structure for table `SampleInfo`
-- 

CREATE TABLE `SampleInfo` (
  `KitID` int(8) unsigned NOT NULL COMMENT 'Lookup to KitInfo to get initial information about the kit (Foreign Key to KitInfo.KitID)',
  `SampleID` varchar(10) NOT NULL COMMENT 'Sample # generated using first two digits are year, then Random for the rest',
  `PatientID` int(8) unsigned NOT NULL COMMENT 'Lookup from Patient Information (Foreign Key for PatientStdInfo.PatientID) ',
  `RecievedDate` date default NULL COMMENT 'Date Sample was Received default to curdate()',
  `CultureDate` date default NULL COMMENT 'Date Culture was done on the sample',
  `ClinicianID` int(8) unsigned NOT NULL COMMENT 'This field will fill in from the KitID but may be changed (Foreign Key ClinicianInfo.ClinicianID)',
  `ClinicID` int(8) unsigned NOT NULL COMMENT 'This field will fill in from the KitID but may be changed (Foreign Key ClinicInfo.CliniciID)',
  `Dx` varchar(128) NOT NULL COMMENT 'Diagnosis Taken from the Sample',
  PRIMARY KEY  (`SampleID`),
  KEY `KitID` (`KitID`),
  KEY `PatientID` (`PatientID`),
  KEY `ClinicianID` (`ClinicianID`),
  KEY `ClinicID` (`ClinicID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Preliminary Sample data taken upons Sample immediate arrival';

-- 
-- Dumping data for table `SampleInfo`
-- 

INSERT INTO `SampleInfo` (`KitID`, `SampleID`, `PatientID`, `RecievedDate`, `CultureDate`, `ClinicianID`, `ClinicID`, `Dx`) VALUES 
(217, '0718216499', 16, '2007-05-03', '2007-04-30', 12, 25, ''),
(218, '0718216513', 18, '2007-05-03', '2007-05-01', 10, 22, ''),
(230, '0738216526', 14, '2007-05-03', '2007-04-23', 10, 22, ''),
(231, '0738216540', 17, '2007-05-03', '2007-05-03', 10, 22, ''),
(237, '0748216554', 15, '2007-05-03', '2007-04-23', 10, 22, ''),
(238, '0748216575', 18, '2007-05-03', '2007-03-11', 10, 22, '');

-- --------------------------------------------------------

-- 
-- Table structure for table `SusceptibilityType`
-- 

CREATE TABLE `SusceptibilityType` (
  `SusceptID` tinyint(3) unsigned NOT NULL auto_increment COMMENT 'Auto Gen ID Number ',
  `FullName` varchar(128) NOT NULL COMMENT 'Full name of the Susceptibility',
  `Abbrev` varchar(2) NOT NULL COMMENT 'Abbreviation of the Susceptibility',
  PRIMARY KEY  (`SusceptID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Serves as a Lookup table for ResultsFinal contains Susceptib' AUTO_INCREMENT=6 ;

-- 
-- Dumping data for table `SusceptibilityType`
-- 

INSERT INTO `SusceptibilityType` (`SusceptID`, `FullName`, `Abbrev`) VALUES 
(1, 'Susceptible', 'S'),
(2, 'Intermediate', 'I'),
(3, 'Resistant', 'R'),
(4, 'Beta Lactomase Detected', 'P'),
(5, 'Beta Lactomase not Detected', 'N');

-- --------------------------------------------------------

-- 
-- Table structure for table `UserInfo`
-- 

CREATE TABLE `UserInfo` (
  `UserID` int(8) NOT NULL auto_increment COMMENT 'Auto Gen ID number for users',
  `Name` varchar(128) NOT NULL COMMENT 'Technician Name',
  `UserName` varchar(128) NOT NULL COMMENT 'Username',
  `Password` varchar(128) NOT NULL COMMENT 'Password',
  `PermissionID` tinyint(2) unsigned NOT NULL COMMENT 'Permission set user is allowed (Lookup from PermissionType.PermissionID)',
  PRIMARY KEY  (`UserID`),
  KEY `PermissionID` (`PermissionID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Database User Information' AUTO_INCREMENT=44 ;

-- 
-- Dumping data for table `UserInfo`
-- 

INSERT INTO `UserInfo` (`UserID`, `Name`, `UserName`, `Password`, `PermissionID`) VALUES 
(1, 'Ben Johnson', 'benjohn', '12345', 1),
(2, 'David Michaelson', 'michaels', 'michaels', 1),
(4, 'John Doe', 'studmuffin', 'stud', 2),
(5, 'Steve Young', 'student', 'student', 3),
(6, 'test', 'test', 'test', 1),
(7, 'technician', 'technician', 'technician', 2),
(43, 'dave', 'dave', '1234', 1);

-- 
-- Constraints for dumped tables
-- 

-- 
-- Constraints for table `CarriesResults`
-- 
ALTER TABLE `CarriesResults`
  ADD CONSTRAINT `CarriesResults_ibfk_1` FOREIGN KEY (`SampleID`) REFERENCES `SampleInfo` (`SampleID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `ClinicianInfo`
-- 
ALTER TABLE `ClinicianInfo`
  ADD CONSTRAINT `ClinicianInfo_ibfk_1` FOREIGN KEY (`ClinicID`) REFERENCES `ClinicInfo` (`ClinicID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `DUWL_Info`
-- 
ALTER TABLE `DUWL_Info`
  ADD CONSTRAINT `DUWL_Info_ibfk_1` FOREIGN KEY (`KitID`) REFERENCES `KitInfo` (`KitID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `KitInfo`
-- 
ALTER TABLE `KitInfo`
  ADD CONSTRAINT `KitInfo_ibfk_3` FOREIGN KEY (`ClinicID`) REFERENCES `ClinicInfo` (`ClinicID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `KitInfo_ibfk_4` FOREIGN KEY (`CulTypeID`) REFERENCES `CultureType` (`CulTypeID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `KitInfo_ibfk_5` FOREIGN KEY (`ClinicianID`) REFERENCES `ClinicianInfo` (`ClinicianID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `PatientBillInfo`
-- 
ALTER TABLE `PatientBillInfo`
  ADD CONSTRAINT `PatientBillInfo_ibfk_1` FOREIGN KEY (`PatientID`) REFERENCES `PatientStdInfo` (`PatientID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `RejectionInfo`
-- 
ALTER TABLE `RejectionInfo`
  ADD CONSTRAINT `RejectionInfo_ibfk_1` FOREIGN KEY (`SampleID`) REFERENCES `SampleInfo` (`SampleID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `RejectionInfo_ibfk_2` FOREIGN KEY (`RCodeID`) REFERENCES `RejectionType` (`RCodeID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `ResultsFinal`
-- 
ALTER TABLE `ResultsFinal`
  ADD CONSTRAINT `ResultsFinal_ibfk_1` FOREIGN KEY (`ResultsID`) REFERENCES `ResultsInitial` (`ResultsID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ResultsFinal_ibfk_2` FOREIGN KEY (`OrgID`) REFERENCES `OrganismType` (`OrgID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `ResultsFinal_ibfk_3` FOREIGN KEY (`GrowthID`) REFERENCES `GrowthType` (`GrowthID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `ResultsFinalMore`
-- 
ALTER TABLE `ResultsFinalMore`
  ADD CONSTRAINT `ResultsFinalMore_ibfk_1` FOREIGN KEY (`ResultsID`) REFERENCES `ResultsInitial` (`ResultsID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ResultsFinalMore_ibfk_2` FOREIGN KEY (`AntibioticID`) REFERENCES `AntibioticType` (`AntibioticID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `ResultsFinalMore_ibfk_3` FOREIGN KEY (`SusceptID`) REFERENCES `SusceptibilityType` (`SusceptID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `ResultsFinalMore_ibfk_4` FOREIGN KEY (`OrgID`) REFERENCES `OrganismType` (`OrgID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `ResultsInitial`
-- 
ALTER TABLE `ResultsInitial`
  ADD CONSTRAINT `ResultsInitial_ibfk_1` FOREIGN KEY (`SampleID`) REFERENCES `SampleInfo` (`SampleID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `SampleInfo`
-- 
ALTER TABLE `SampleInfo`
  ADD CONSTRAINT `SampleInfo_ibfk_1` FOREIGN KEY (`KitID`) REFERENCES `KitInfo` (`KitID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `SampleInfo_ibfk_2` FOREIGN KEY (`PatientID`) REFERENCES `PatientStdInfo` (`PatientID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `SampleInfo_ibfk_3` FOREIGN KEY (`ClinicianID`) REFERENCES `ClinicianInfo` (`ClinicianID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `SampleInfo_ibfk_4` FOREIGN KEY (`ClinicID`) REFERENCES `ClinicInfo` (`ClinicID`) ON DELETE NO ACTION ON UPDATE CASCADE;

-- 
-- Constraints for table `UserInfo`
-- 
ALTER TABLE `UserInfo`
  ADD CONSTRAINT `UserInfo_ibfk_1` FOREIGN KEY (`PermissionID`) REFERENCES `PermissionType` (`PermissionID`) ON DELETE NO ACTION ON UPDATE CASCADE;