Re: Database Schema

[Peter Peterson]

Rick,
I think I have worked out what to do - I trust the following is what you are looking for:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `massageDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `massageDB` ;

-- -----------------------------------------------------
-- Table `massageDB`.`Titles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Titles` (
`idTitle` INT NOT NULL AUTO_INCREMENT,
`Title` VARCHAR(25) NULL,
`TitleAbrev` VARCHAR(5) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idTitle`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Occupations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Occupations` (
`idOccupation` INT NOT NULL AUTO_INCREMENT,
`Occupation` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idOccupation`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`States`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`States` (
`idState` INT NOT NULL AUTO_INCREMENT,
`State` VARCHAR(45) NULL,
`StateAbbreviation` VARCHAR(3) NULL,
`PreferredOrder` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idState`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Suburbs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Suburbs` (
`idSuburb` INT NOT NULL AUTO_INCREMENT,
`Suburb` VARCHAR(45) NULL,
`Postcode` VARCHAR(4) NULL,
`State` INT NULL,
`UsedCounter` INT NULL,
`Deactivate` TINYINT(1) NULL,
`States_idStates` INT NOT NULL,
PRIMARY KEY (`idSuburb`, `States_idStates`),
INDEX `fk_Suburbs_States1_idx` (`States_idStates` ASC),
CONSTRAINT `fk_Suburbs_States1`
FOREIGN KEY (`States_idStates`)
REFERENCES `massageDB`.`States` (`idState`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`HealthFunds`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`HealthFunds` (
`idHealthFund` INT NOT NULL AUTO_INCREMENT,
`HealthFund` VARCHAR(45) NULL,
`UsageCounter` INT NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idHealthFund`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`MedicalCentres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`MedicalCentres` (
`idMedicalCentre` INT NOT NULL AUTO_INCREMENT,
`MedicalCentre` VARCHAR(45) NULL,
`Address` VARCHAR(60) NULL,
`Suburb` INT NULL,
`Phone` VARCHAR(10) NULL,
`Email` VARCHAR(60) NULL,
`Website` VARCHAR(45) NULL,
`ContactPerson` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idMedicalCentre`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Doctors`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Doctors` (
`idDoctor` INT NOT NULL AUTO_INCREMENT,
`Surname` VARCHAR(45) NULL,
`First Name` VARCHAR(45) NULL,
`Phone` VARCHAR(10) NULL,
`Email` VARCHAR(60) NULL,
`Deactivate` TINYINT(1) NULL,
`MedicalCentres_idMedicalCentre` INT NOT NULL,
`Titles_idTitle` INT NOT NULL,
PRIMARY KEY (`idDoctor`, `MedicalCentres_idMedicalCentre`, `Titles_idTitle`),
INDEX `fk_Doctors_MedicalCentres1_idx` (`MedicalCentres_idMedicalCentre` ASC),
INDEX `fk_Doctors_Titles1_idx` (`Titles_idTitle` ASC),
CONSTRAINT `fk_Doctors_MedicalCentres1`
FOREIGN KEY (`MedicalCentres_idMedicalCentre`)
REFERENCES `massageDB`.`MedicalCentres` (`idMedicalCentre`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Doctors_Titles1`
FOREIGN KEY (`Titles_idTitle`)
REFERENCES `massageDB`.`Titles` (`idTitle`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`AdvertisingSources`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`AdvertisingSources` (
`idAdvertisingSource` INT NOT NULL AUTO_INCREMENT,
`AdvertisingSource` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idAdvertisingSource`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Country`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Country` (
`idCountry` INT NOT NULL AUTO_INCREMENT,
`Country` VARCHAR(45) NOT NULL,
`ISOAbbreviation` VARCHAR(2) NOT NULL,
`OlympicAbbreviation` VARCHAR(3) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idCountry`),
UNIQUE INDEX `ISOAbbreviation_UNIQUE` (`ISOAbbreviation` ASC),
UNIQUE INDEX `Country_UNIQUE` (`Country` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Clients` (
`idClient` INT NOT NULL AUTO_INCREMENT,
`Surname` VARCHAR(45) NOT NULL,
`Firstname` VARCHAR(45) NOT NULL,
`PreferredName` VARCHAR(45) NULL,
`DOB` DATETIME NULL,
`SeniorsCard` TINYINT(1) NULL,
`Address` VARCHAR(60) NULL,
`Clientscol` VARCHAR(45) NULL,
`Mobile` VARCHAR(10) NULL,
`HomePhone` VARCHAR(10) NULL,
`WorkPhone` VARCHAR(10) NULL,
`Email` VARCHAR(60) NULL,
`MedicareNumber` VARCHAR(10) NULL,
`Injuries` BLOB NULL,
`Surgeries` BLOB NULL,
`Medications` BLOB NULL,
`InfectiousDiseases` BLOB NULL,
`LiaiseWithDoctor` TINYINT(1) NULL,
`ReceiveNewsletter` TINYINT(1) NULL,
`Deceased` TINYINT(1) NULL,
`LostContact` TINYINT(1) NULL,
`Comments` BLOB NULL,
`Titles_idTitles` INT NOT NULL,
`Occupations_idOccupations` INT NOT NULL,
`Suburbs_idSuburbs` INT NOT NULL,
`HealthFunds_idHealthFund` INT NOT NULL,
`Doctors_idDoctor` INT NOT NULL,
`AdvertisingSources_idAdvertisingSource` INT NOT NULL,
`Country_idCountry` INT NOT NULL,
PRIMARY KEY (`idClient`, `Titles_idTitles`, `Occupations_idOccupations`, `Suburbs_idSuburbs`, `HealthFunds_idHealthFund`, `Doctors_idDoctor`, `AdvertisingSources_idAdvertisingSource`, `Country_idCountry`),
INDEX `fk_Clients_Titles_idx` (`Titles_idTitles` ASC),
INDEX `fk_Clients_Occupations1_idx` (`Occupations_idOccupations` ASC),
INDEX `fk_Clients_Suburbs1_idx` (`Suburbs_idSuburbs` ASC),
INDEX `fk_Clients_HealthFunds1_idx` (`HealthFunds_idHealthFund` ASC),
INDEX `fk_Clients_Doctors1_idx` (`Doctors_idDoctor` ASC),
INDEX `fk_Clients_AdvertisingSources1_idx` (`AdvertisingSources_idAdvertisingSource` ASC),
INDEX `fk_Clients_Country1_idx` (`Country_idCountry` ASC),
INDEX `ClientName` (`Surname` ASC, `Firstname` ASC),
CONSTRAINT `fk_Clients_Titles`
FOREIGN KEY (`Titles_idTitles`)
REFERENCES `massageDB`.`Titles` (`idTitle`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_Occupations1`
FOREIGN KEY (`Occupations_idOccupations`)
REFERENCES `massageDB`.`Occupations` (`idOccupation`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_Suburbs1`
FOREIGN KEY (`Suburbs_idSuburbs`)
REFERENCES `massageDB`.`Suburbs` (`idSuburb`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_HealthFunds1`
FOREIGN KEY (`HealthFunds_idHealthFund`)
REFERENCES `massageDB`.`HealthFunds` (`idHealthFund`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_Doctors1`
FOREIGN KEY (`Doctors_idDoctor`)
REFERENCES `massageDB`.`Doctors` (`idDoctor`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_AdvertisingSources1`
FOREIGN KEY (`AdvertisingSources_idAdvertisingSource`)
REFERENCES `massageDB`.`AdvertisingSources` (`idAdvertisingSource`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Clients_Country1`
FOREIGN KEY (`Country_idCountry`)
REFERENCES `massageDB`.`Country` (`idCountry`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`DiscountTypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`DiscountTypes` (
`idDiscountType` INT NOT NULL AUTO_INCREMENT,
`DiscountType` VARCHAR(45) NULL,
`DiscountPercentage` DECIMAL(2) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idDiscountType`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`ServiceTypes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`ServiceTypes` (
`idServiceType` INT NOT NULL AUTO_INCREMENT,
`ServiceType` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idServiceType`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`PaymentMethods`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`PaymentMethods` (
`idPaymentMethod` INT NOT NULL AUTO_INCREMENT,
`PaymentMethod` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idPaymentMethod`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`VisitReasons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`VisitReasons` (
`idVisitReason` INT NOT NULL AUTO_INCREMENT,
`VisitReason` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idVisitReason`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`VisitDurations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`VisitDurations` (
`idVisitDuration` INT NOT NULL AUTO_INCREMENT,
`VisitDuration` INT NULL,
`VisitPrice` DECIMAL(2) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idVisitDuration`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`VisitDetails`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`VisitDetails` (
`idVisitDetail` INT NOT NULL AUTO_INCREMENT,
`VisitDate` DATETIME NULL,
`VisitTime` DATETIME NULL,
`Discount` DECIMAL(2) NULL,
`Comments` BLOB NULL,
`Documents` BLOB NULL,
`Clients_idClient` INT NOT NULL,
`Clients_Titles_idTitles` INT NOT NULL,
`Clients_Occupations_idOccupations` INT NOT NULL,
`Clients_Suburbs_idSuburbs` INT NOT NULL,
`Clients_HealthFunds_idHealthFund` INT NOT NULL,
`ServiceTypes_idServiceType` INT NOT NULL,
`Doctors_idDoctor` INT NOT NULL,
`PaymentMethods_idPaymentMethod` INT NOT NULL,
`VisitDurations_idVisitDuration` INT NOT NULL,
`VisitReasons_idVisitReason` INT NOT NULL,
`DiscountTypes_idDiscountType` INT NOT NULL,
PRIMARY KEY (`idVisitDetail`, `Clients_idClient`, `Clients_Titles_idTitles`, `Clients_Occupations_idOccupations`, `Clients_Suburbs_idSuburbs`, `Clients_HealthFunds_idHealthFund`, `ServiceTypes_idServiceType`, `Doctors_idDoctor`, `PaymentMethods_idPaymentMethod`, `VisitDurations_idVisitDuration`, `VisitReasons_idVisitReason`, `DiscountTypes_idDiscountType`),
INDEX `fk_VisitDetails_Clients1_idx` (`Clients_idClient` ASC, `Clients_Titles_idTitles` ASC, `Clients_Occupations_idOccupations` ASC, `Clients_Suburbs_idSuburbs` ASC, `Clients_HealthFunds_idHealthFund` ASC),
INDEX `fk_VisitDetails_ServiceTypes1_idx` (`ServiceTypes_idServiceType` ASC),
INDEX `fk_VisitDetails_Doctors1_idx` (`Doctors_idDoctor` ASC),
INDEX `fk_VisitDetails_PaymentMethods1_idx` (`PaymentMethods_idPaymentMethod` ASC),
INDEX `fk_VisitDetails_VisitDurations1_idx` (`VisitDurations_idVisitDuration` ASC),
INDEX `fk_VisitDetails_VisitReasons1_idx` (`VisitReasons_idVisitReason` ASC),
INDEX `fk_VisitDetails_DiscountTypes1_idx` (`DiscountTypes_idDiscountType` ASC),
CONSTRAINT `fk_VisitDetails_Clients1`
FOREIGN KEY (`Clients_idClient` , `Clients_Titles_idTitles` , `Clients_Occupations_idOccupations` , `Clients_Suburbs_idSuburbs` , `Clients_HealthFunds_idHealthFund`)
REFERENCES `massageDB`.`Clients` (`idClient` , `Titles_idTitles` , `Occupations_idOccupations` , `Suburbs_idSuburbs` , `HealthFunds_idHealthFund`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_VisitDetails_ServiceTypes1`
FOREIGN KEY (`ServiceTypes_idServiceType`)
REFERENCES `massageDB`.`ServiceTypes` (`idServiceType`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_VisitDetails_Doctors1`
FOREIGN KEY (`Doctors_idDoctor`)
REFERENCES `massageDB`.`Doctors` (`idDoctor`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_VisitDetails_PaymentMethods1`
FOREIGN KEY (`PaymentMethods_idPaymentMethod`)
REFERENCES `massageDB`.`PaymentMethods` (`idPaymentMethod`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_VisitDetails_VisitDurations1`
FOREIGN KEY (`VisitDurations_idVisitDuration`)
REFERENCES `massageDB`.`VisitDurations` (`idVisitDuration`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_VisitDetails_VisitReasons1`
FOREIGN KEY (`VisitReasons_idVisitReason`)
REFERENCES `massageDB`.`VisitReasons` (`idVisitReason`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_VisitDetails_DiscountTypes1`
FOREIGN KEY (`DiscountTypes_idDiscountType`)
REFERENCES `massageDB`.`DiscountTypes` (`idDiscountType`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`UserLevels`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`UserLevels` (
`idUserLevel` INT NOT NULL AUTO_INCREMENT,
`UserLevel` INT NULL,
`LevelDescription` VARCHAR(45) NULL,
`Deactivate` TINYINT(1) NULL,
PRIMARY KEY (`idUserLevel`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Users` (
`idUser` INT NOT NULL AUTO_INCREMENT,
`Username` VARCHAR(45) NULL,
`Password` VARCHAR(45) NULL,
`UserLevel` INT NULL,
`Deactivate` TINYINT(1) NULL,
`UserLevels_idUserLevel` INT NOT NULL,
PRIMARY KEY (`idUser`, `UserLevels_idUserLevel`),
INDEX `fk_Users_UserLevels1_idx` (`UserLevels_idUserLevel` ASC),
CONSTRAINT `fk_Users_UserLevels1`
FOREIGN KEY (`UserLevels_idUserLevel`)
REFERENCES `massageDB`.`UserLevels` (`idUserLevel`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `massageDB`.`Appointments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `massageDB`.`Appointments` (
`idAppointments` INT NOT NULL,
`Date` DATETIME NULL,
`FromTime` DATETIME NULL,
`ToTime` DATETIME NULL,
`idClient` INT NULL,
`GiftVoucher` TINYINT(1) NULL,
`Clients_idClient` INT NOT NULL,
`Clients_Titles_idTitles` INT NOT NULL,
`Clients_Occupations_idOccupations` INT NOT NULL,
`Clients_Suburbs_idSuburbs` INT NOT NULL,
`Clients_HealthFunds_idHealthFund` INT NOT NULL,
`Clients_Doctors_idDoctor` INT NOT NULL,
`Clients_AdvertisingSources_idAdvertisingSource` INT NOT NULL,
`Clients_Country_idCountry` INT NOT NULL,
PRIMARY KEY (`idAppointments`),
INDEX `fk_Appointments_Clients1_idx` (`Clients_idClient` ASC, `Clients_Titles_idTitles` ASC, `Clients_Occupations_idOccupations` ASC, `Clients_Suburbs_idSuburbs` ASC, `Clients_HealthFunds_idHealthFund` ASC, `Clients_Doctors_idDoctor` ASC, `Clients_AdvertisingSources_idAdvertisingSource` ASC, `Clients_Country_idCountry` ASC),
CONSTRAINT `fk_Appointments_Clients1`
FOREIGN KEY (`Clients_idClient` , `Clients_Titles_idTitles` , `Clients_Occupations_idOccupations` , `Clients_Suburbs_idSuburbs` , `Clients_HealthFunds_idHealthFund` , `Clients_Doctors_idDoctor` , `Clients_AdvertisingSources_idAdvertisingSource` , `Clients_Country_idCountry`)
REFERENCES `massageDB`.`Clients` (`idClient` , `Titles_idTitles` , `Occupations_idOccupations` , `Suburbs_idSuburbs` , `HealthFunds_idHealthFund` , `Doctors_idDoctor` , `AdvertisingSources_idAdvertisingSource` , `Country_idCountry`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Thank you in anticipation of your time and help.
Kind regards
Peter