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';

CREATE SCHEMA IF NOT EXISTS `bien_web` DEFAULT CHARACTER SET latin1 ;
USE `bien_web` ;

-- -----------------------------------------------------
-- Table `bien_web`.`datasource`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`datasource` (
  `dataSourceID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `dataSourceName` VARCHAR(100) NULL DEFAULT NULL ,
  `aggregatorOrPrimary` VARCHAR(25) NULL DEFAULT NULL ,
  `sourceType` VARCHAR(25) NULL DEFAULT NULL ,
  `accessLevel` VARCHAR(50) NULL DEFAULT NULL ,
  `accessConditions` VARCHAR(500) NULL DEFAULT NULL ,
  `authorshipContactEmail` VARCHAR(150) NULL DEFAULT NULL ,
  PRIMARY KEY (`dataSourceID`) ,
  UNIQUE INDEX `dataSourceName` (`dataSourceName` ASC) ,
  INDEX `aggregatorOrPrimary` (`aggregatorOrPrimary` ASC) ,
  INDEX `sourceType` (`sourceType` ASC) ,
  INDEX `accessLevel` (`accessLevel` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bien_web`.`dataSourceNormalized`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`dataSourceNormalized` (
  `dataSourceNormalizedID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `dataSourceID` INT(11) UNSIGNED NOT NULL ,
  `dataSourceNameCorrected` VARCHAR(255) NOT NULL ,
  `isHerbarium` INT(1) NOT NULL DEFAULT 0 ,
  `isAggregator` INT(1) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`dataSourceNormalizedID`) ,
  INDEX `dataSourceID` (`dataSourceID` ASC) ,
  INDEX `dataSourceName` (`dataSourceNameCorrected` ASC) ,
  INDEX `isHerbarium` (`isHerbarium` ASC) ,
  INDEX `fk_dataSourceNameCorrected_datasource1` (`dataSourceID` ASC) ,
  CONSTRAINT `fk_dataSourceNameCorrected_datasource1`
    FOREIGN KEY (`dataSourceID` )
    REFERENCES `bien_web`.`datasource` (`dataSourceID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1578
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `bien_web`.`party`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`party` (
  `partyID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `partyType` VARCHAR(20) NOT NULL DEFAULT 'person' COMMENT '\'person\',\'institution\'' ,
  `partyFullName` VARCHAR(100) NOT NULL ,
  `userName` VARCHAR(50) NULL DEFAULT NULL ,
  `lastName` VARCHAR(50) NULL DEFAULT NULL ,
  `firstName` VARCHAR(50) NULL DEFAULT NULL ,
  `email` VARCHAR(150) NULL DEFAULT NULL ,
  `primaryInstitution` VARCHAR(255) NULL DEFAULT NULL ,
  `institutionName` VARCHAR(150) NULL DEFAULT NULL ,
  `department` VARCHAR(150) NULL DEFAULT NULL ,
  `address1` VARCHAR(150) NULL DEFAULT NULL ,
  `address2` VARCHAR(150) NULL DEFAULT NULL ,
  `city` VARCHAR(50) NULL DEFAULT NULL ,
  `stateProvince` VARCHAR(50) NULL DEFAULT NULL ,
  `postalCode` VARCHAR(25) NULL DEFAULT NULL ,
  `country` VARCHAR(50) NULL DEFAULT NULL ,
  `stateProvinceCode` VARCHAR(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`partyID`) ,
  INDEX `partyType` (`partyType` ASC) ,
  INDEX `lastName` (`lastName` ASC) ,
  INDEX `firstName` (`firstName` ASC) ,
  INDEX `fullName` (`partyFullName` ASC) ,
  INDEX `institutionName` (`institutionName` ASC) ,
  INDEX `country` (`country` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bien_web`.`datasource_party`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`datasource_party` (
  `datasourceNormalized_party_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `dataSourceNormalizedID` INT(11) UNSIGNED NOT NULL ,
  `partyID` INT(11) UNSIGNED NOT NULL ,
  `userRole` VARCHAR(50) NULL DEFAULT NULL ,
  `isPI` INT(1) NULL DEFAULT NULL ,
  UNIQUE INDEX `user_datasource` (`partyID` ASC, `dataSourceNormalizedID` ASC) ,
  INDEX `userRole` (`userRole` ASC) ,
  INDEX `isPI` (`isPI` ASC) ,
  INDEX `fk_datasource_party_party1` (`partyID` ASC) ,
  INDEX `fk_datasource_party_dataSourceNormalized1` (`dataSourceNormalizedID` ASC) ,
  PRIMARY KEY (`datasourceNormalized_party_ID`) ,
  UNIQUE INDEX `datasourceNormalized_party_ID_UNIQUE` (`datasourceNormalized_party_ID` ASC) ,
  CONSTRAINT `fk_datasource_party_party1`
    FOREIGN KEY (`partyID` )
    REFERENCES `bien_web`.`party` (`partyID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_datasource_party_dataSourceNormalized1`
    FOREIGN KEY (`dataSourceNormalizedID` )
    REFERENCES `bien_web`.`dataSourceNormalized` (`dataSourceNormalizedID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bien_web`.`plot`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`plot` (
  `plotID` INT(11) UNSIGNED NOT NULL COMMENT '=DBPlotID from bien2' ,
  `dataSourceID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `dataSourceName` VARCHAR(100) NULL DEFAULT NULL ,
  `plotCode` VARCHAR(100) NULL DEFAULT NULL ,
  `plotAreaHa` DOUBLE NULL DEFAULT NULL ,
  `plotMinDbh` DECIMAL(5,1) NULL DEFAULT NULL ,
  `plotMethod` VARCHAR(100) NULL DEFAULT NULL ,
  `country` VARCHAR(50) NULL DEFAULT NULL ,
  `stateProvince` VARCHAR(100) NULL DEFAULT NULL ,
  `countyParish` VARCHAR(100) NULL DEFAULT NULL ,
  `latitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `longitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
  `isGeovalid` INT(1) NULL DEFAULT NULL ,
  `elevation_m` DECIMAL(6,1) NULL DEFAULT NULL ,
  `localityDescription` VARCHAR(500) NULL DEFAULT NULL ,
  PRIMARY KEY (`plotID`) ,
  INDEX `dataSourceName` (`dataSourceName` ASC) ,
  INDEX `dataSourceID` (`dataSourceID` ASC) ,
  INDEX `plotID` (`plotID` ASC) ,
  INDEX `country` (`country` ASC) ,
  INDEX `stateProvince` (`stateProvince` ASC) ,
  INDEX `plotCode` (`plotCode` ASC) ,
  INDEX `plotAreaHa` (`plotAreaHa` ASC) ,
  INDEX `plotMinDbh` (`plotMinDbh` ASC) ,
  INDEX `plotMethod` (`plotMethod` ASC) ,
  INDEX `isValidLatLong` (`isValidLatLong` ASC) ,
  INDEX `isGeovalid` (`isGeovalid` ASC) ,
  INDEX `fk_plot_datasource1` (`dataSourceID` ASC) ,
  CONSTRAINT `fk_plot_datasource1`
    FOREIGN KEY (`dataSourceID` )
    REFERENCES `bien_web`.`datasource` (`dataSourceID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bien_web`.`taxon`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`taxon` (
  `taxonID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `parentTaxonID` INT(10) UNSIGNED NULL DEFAULT NULL ,
  `taxon` VARCHAR(255) NOT NULL ,
  `rank` VARCHAR(255) NULL DEFAULT NULL ,
  `nameClass` VARCHAR(50) NULL DEFAULT NULL ,
  `taxonAuthor` VARCHAR(255) NULL DEFAULT NULL ,
  `family` VARCHAR(150) NULL DEFAULT NULL ,
  `genus` VARCHAR(100) NULL DEFAULT NULL ,
  `species` VARCHAR(100) NULL DEFAULT NULL ,
  `leftIndex` INT(11) NULL DEFAULT NULL ,
  `rightIndex` INT(11) NULL DEFAULT NULL ,
  `isAccepted` INT(1) NULL DEFAULT NULL ,
  `isHybrid` INT(10) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`taxonID`) ,
  UNIQUE INDEX `leftIndex` (`leftIndex` ASC) ,
  UNIQUE INDEX `rightIndex` (`rightIndex` ASC) ,
  INDEX `taxon` (`taxon` ASC) ,
  INDEX `taxonAuthor` (`taxonAuthor` ASC) ,
  INDEX `rank` (`rank` ASC) ,
  INDEX `nameClass` (`nameClass` ASC) ,
  INDEX `family` (`family` ASC) ,
  INDEX `genus` (`genus` ASC) ,
  INDEX `species` (`species` ASC) ,
  INDEX `isHybrid` (`isHybrid` ASC) ,
  INDEX `parentTaxonID` (`parentTaxonID` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `bien_web`.`observation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `bien_web`.`observation` (
  `observationID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `dataSourceID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `dataSourceName` VARCHAR(100) NULL DEFAULT NULL ,
  `dataSourceNamePrimary` VARCHAR(100) NULL DEFAULT NULL ,
  `plotID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `observationType` VARCHAR(10) NULL DEFAULT NULL COMMENT '=plot,specimen,trait)' ,
  `bien2_taxonomyID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `bien2_DBPlotID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `bien2_ObservationID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `bien2_OccurID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `bien2_TraitObservationID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `taxonAuthorityVerbatim` VARCHAR(255) NULL DEFAULT NULL ,
  `higherPlantGroup` VARCHAR(25) NULL DEFAULT NULL ,
  `taxonID` INT(10) UNSIGNED NULL ,
  `family` VARCHAR(100) NULL DEFAULT NULL ,
  `genus` VARCHAR(100) NULL DEFAULT NULL ,
  `species` VARCHAR(150) NULL DEFAULT NULL ,
  `taxon` VARCHAR(200) NULL DEFAULT NULL ,
  `taxonAuthor` VARCHAR(255) NULL DEFAULT NULL ,
  `taxonMorphospecies` VARCHAR(255) NULL DEFAULT NULL ,
  `rank` VARCHAR(50) NULL DEFAULT NULL ,
  `acceptance` VARCHAR(2) NULL DEFAULT NULL ,
  `country` VARCHAR(50) NULL DEFAULT NULL ,
  `stateProvince` VARCHAR(100) NULL DEFAULT NULL ,
  `countyParish` VARCHAR(100) NULL DEFAULT NULL ,
  `countryError` DOUBLE NULL DEFAULT NULL ,
  `stateProvinceError` DOUBLE NULL DEFAULT NULL ,
  `localityDescription` VARCHAR(500) NULL DEFAULT NULL ,
  `collector` VARCHAR(150) NULL DEFAULT NULL ,
  `collectionNumber` VARCHAR(50) NULL DEFAULT NULL ,
  `identifiedBy` VARCHAR(150) NULL DEFAULT NULL ,
  `observationDate` DATE NULL DEFAULT NULL ,
  `plotCode` VARCHAR(100) NULL DEFAULT NULL ,
  `plotAreaHa` DOUBLE NULL DEFAULT NULL ,
  `plotMinDbh` DECIMAL(5,1) NULL DEFAULT NULL ,
  `plotMethod` VARCHAR(100) NULL DEFAULT NULL ,
  `latitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `longitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
  `isGeovalid` INT(1) NULL DEFAULT NULL ,
  `isNewWorld` INT(1) NULL DEFAULT NULL ,
  `elevation_m` DECIMAL(6,1) NULL DEFAULT NULL ,
  `isCultivated` INT(1) NULL DEFAULT '0' ,
  `isCultivatedReason` VARCHAR(255) NULL DEFAULT NULL ,
  `abund` INT(11) NULL DEFAULT NULL ,
  `abund1` INT(11) NULL DEFAULT NULL ,
  `abund2.5` INT(11) NULL DEFAULT NULL ,
  `abund10` INT(11) NULL DEFAULT NULL ,
  `pctCover` DECIMAL(10,5) NULL DEFAULT NULL ,
  `traitName` VARCHAR(200) NULL DEFAULT NULL ,
  `traitValue` VARCHAR(100) NULL DEFAULT NULL ,
  `traitUnit` VARCHAR(100) NULL DEFAULT NULL ,
  `traitMethod` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`observationID`) ,
  INDEX `observationID` (`observationID` ASC) ,
  INDEX `dataSourceID` (`dataSourceID` ASC) ,
  INDEX `dataSourceName` (`dataSourceName` ASC) ,
  INDEX `dataSourceNamePrimary` (`dataSourceNamePrimary` ASC) ,
  INDEX `plotID` (`plotID` ASC) ,
  INDEX `observationType` (`observationType` ASC) ,
  INDEX `bien2_taxonomyID` (`bien2_taxonomyID` ASC) ,
  INDEX `bien2_DBPlotID` (`bien2_DBPlotID` ASC) ,
  INDEX `bien2_ObservationID` (`bien2_ObservationID` ASC) ,
  INDEX `bien2_OccurID` (`bien2_OccurID` ASC) ,
  INDEX `taxonAuthorityVerbatim` (`taxonAuthorityVerbatim` ASC) ,
  INDEX `higherPlantGroup` (`higherPlantGroup` ASC) ,
  INDEX `family` (`family` ASC) ,
  INDEX `genus` (`genus` ASC) ,
  INDEX `species` (`species` ASC) ,
  INDEX `taxon` (`taxon` ASC) ,
  INDEX `taxonAuthor` (`taxonAuthor` ASC) ,
  INDEX `taxonMorphospecies` (`taxonMorphospecies` ASC) ,
  INDEX `rank` (`rank` ASC) ,
  INDEX `acceptance` (`acceptance` ASC) ,
  INDEX `country` (`country` ASC) ,
  INDEX `stateProvince` (`stateProvince` ASC) ,
  INDEX `collector` (`collector` ASC) ,
  INDEX `identifiedBy` (`identifiedBy` ASC) ,
  INDEX `plotCode` (`plotCode` ASC) ,
  INDEX `plotAreaHa` (`plotAreaHa` ASC) ,
  INDEX `plotMinDbh` (`plotMinDbh` ASC) ,
  INDEX `plotMethod` (`plotMethod` ASC) ,
  INDEX `isValidLatLong` (`isValidLatLong` ASC) ,
  INDEX `isGeovalid` (`isGeovalid` ASC) ,
  INDEX `isNewWorld` (`isNewWorld` ASC) ,
  INDEX `isCultivated` (`isCultivated` ASC) ,
  INDEX `isCultivatedReason` (`isCultivatedReason` ASC) ,
  INDEX `bien2_TraitObservationID` (`bien2_TraitObservationID` ASC) ,
  INDEX `traitName` (`traitName` ASC) ,
  INDEX `traitMethod` (`traitMethod` ASC) ,
  INDEX `traitUnit` (`traitUnit` ASC) ,
  INDEX `fk_observation_datasource` (`dataSourceID` ASC) ,
  INDEX `fk_observation_plot1` (`plotID` ASC) ,
  INDEX `fk_observation_taxon1` (`taxonID` ASC) ,
  CONSTRAINT `fk_observation_datasource`
    FOREIGN KEY (`dataSourceID` )
    REFERENCES `bien_web`.`datasource` (`dataSourceID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_observation_plot1`
    FOREIGN KEY (`plotID` )
    REFERENCES `bien_web`.`plot` (`plotID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_observation_taxon1`
    FOREIGN KEY (`taxonID` )
    REFERENCES `bien_web`.`taxon` (`taxonID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
AUTO_INCREMENT = 10052218
DEFAULT CHARACTER SET = utf8;



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