Project

General

Profile

« Previous | Next » 

Revision 8579

Added by Aaron Marcuse-Kubitza almost 12 years ago

schemas/VegCore/VegCore.ERD.mwb: added primary columns, including catch-all hstores for custom columns. made stem inherit from individual and stem_observation from individual_observation because any stem can have individual-level traits and identifying info.

View differences:

schemas/VegCore/VegCore.my.sql
9 9 
CREATE  TABLE IF NOT EXISTS `source` (
10 10 
  `id` TEXT NOT NULL ,
11 11 
  `parent` TEXT NOT NULL ,
12 
  `info` SET('hstore') NULL ,
12 13 
  PRIMARY KEY (`id`) ,
13 14 
  INDEX `fk_source1` (`parent` ASC) ,
14 15 
  CONSTRAINT `fk_source1`
......
46 47 
  `id` TEXT NOT NULL ,
47 48 
  `record` TEXT NOT NULL ,
48 49 
  `related_record` TEXT NOT NULL ,
50 
  `info` SET('hstore') NULL ,
49 51 
  PRIMARY KEY (`id`) ,
50 52 
  INDEX `fk_relationship_record1` (`record` ASC) ,
51 53 
  INDEX `fk_relationship_related_record` (`related_record` ASC) ,
......
176 178 
-- -----------------------------------------------------
177 179 
CREATE  TABLE IF NOT EXISTS `party` (
178 180 
  `id` TEXT NOT NULL ,
181 
  `info` SET('hstore') NULL ,
179 182 
  PRIMARY KEY (`id`) ,
180 183 
  CONSTRAINT `fk_collection_source10`
181 184 
    FOREIGN KEY (`id` )
......
243 246 
-- -----------------------------------------------------
244 247 
CREATE  TABLE IF NOT EXISTS `place_path` (
245 248 
  `id` TEXT NOT NULL ,
249 
  `continent` VARCHAR(45) NULL ,
250 
  `country` VARCHAR(45) NULL ,
251 
  `state_province` VARCHAR(45) NULL ,
252 
  `county` VARCHAR(45) NULL ,
253 
  `municipality` VARCHAR(45) NULL ,
254 
  `ranks` SET('hstore') NULL ,
246 255 
  PRIMARY KEY (`id`) )
247 256 
ENGINE = InnoDB
248 257 
DEFAULT CHARACTER SET = latin1
......
255 264 
CREATE  TABLE IF NOT EXISTS `place` (
256 265 
  `id` TEXT NOT NULL ,
257 266 
  `parent` TEXT NOT NULL ,
267 
  `coordinates` TEXT NULL ,
258 268 
  `path` TEXT NULL ,
259 
  `coordinates` TEXT NULL ,
269 
  `locality` VARCHAR(45) NULL ,
260 270 
  PRIMARY KEY (`id`) ,
261 271 
  INDEX `fk_place_coordinates1` (`coordinates` ASC) ,
262 272 
  INDEX `fk_place1` (`parent` ASC) ,
......
291 301 
-- -----------------------------------------------------
292 302 
CREATE  TABLE IF NOT EXISTS `method` (
293 303 
  `id` TEXT NOT NULL ,
304 
  `info` SET('hstore') NULL ,
294 305 
  PRIMARY KEY (`id`) ,
295 306 
  CONSTRAINT `fk_method_record1`
296 307 
    FOREIGN KEY (`id` )
......
308 319 
CREATE  TABLE IF NOT EXISTS `event` (
309 320 
  `id` TEXT NOT NULL ,
310 321 
  `parent` TEXT NOT NULL ,
322 
  `name` TEXT NULL ,
311 323 
  `place` TEXT NULL ,
312 324 
  `method` TEXT NULL ,
313 325 
  PRIMARY KEY (`id`) ,
......
346 358 
  `id` TEXT NOT NULL ,
347 359 
  `current_determination` TEXT NULL ,
348 360 
  `original_determination` TEXT NULL ,
361 
  `cultivated` TINYINT(1) NULL ,
349 362 
  `traits` SET('hstore') NULL ,
350 363 
  PRIMARY KEY (`id`) ,
351 364 
  INDEX `fk_taxon_occurrence_taxon_determination1` (`original_determination` ASC) ,
......
445 458 
-- -----------------------------------------------------
446 459 
CREATE  TABLE IF NOT EXISTS `individual` (
447 460 
  `id` TEXT NOT NULL ,
461 
  `tag` TEXT NULL ,
462 
  `author_code` VARCHAR(45) NULL ,
448 463 
  PRIMARY KEY (`id`) ,
449 464 
  CONSTRAINT `fk_individual_record1`
450 465 
    FOREIGN KEY (`id` )
......
462 477 
CREATE  TABLE IF NOT EXISTS `individual_observation` (
463 478 
  `id` TEXT NOT NULL ,
464 479 
  `individual` TEXT NOT NULL ,
480 
  `traits` SET('hstore') NULL ,
465 481 
  PRIMARY KEY (`id`) ,
466 482 
  INDEX `fk_individual_observation_individual1` (`individual` ASC) ,
467 483 
  CONSTRAINT `fk_individual_observation_taxon_occurrence1`
......
487 503 
  `individual` TEXT NOT NULL ,
488 504 
  PRIMARY KEY (`id`) ,
489 505 
  INDEX `fk_stem_individual1` (`individual` ASC) ,
490 
  CONSTRAINT `fk_stem_record1`
491 
    FOREIGN KEY (`id` )
492 
    REFERENCES `record` (`id` )
493 
    ON DELETE CASCADE
494 
    ON UPDATE CASCADE,
495 506 
  CONSTRAINT `fk_stem_individual1`
496 507 
    FOREIGN KEY (`individual` )
497 508 
    REFERENCES `individual` (`id` )
498 509 
    ON DELETE CASCADE
510 
    ON UPDATE CASCADE,
511 
  CONSTRAINT `fk_stem_individual2`
512 
    FOREIGN KEY (`id` )
513 
    REFERENCES `individual` (`id` )
514 
    ON DELETE CASCADE
499 515 
    ON UPDATE CASCADE)
500 516 
ENGINE = InnoDB
501 517 
DEFAULT CHARACTER SET = latin1
......
509 525 
  `id` TEXT NOT NULL ,
510 526 
  `individual_observation` TEXT NOT NULL ,
511 527 
  `stem` TEXT NOT NULL ,
528 
  `traits` SET('hstore') NULL ,
512 529 
  PRIMARY KEY (`id`) ,
513 530 
  INDEX `fk_stem_observation_individual_observation1` (`individual_observation` ASC) ,
514 531 
  INDEX `fk_stem_observation_stem1` (`stem` ASC) ,
515 532 
  UNIQUE INDEX `stem_observation_unique` (`individual_observation` ASC, `stem` ASC) ,
516 
  CONSTRAINT `fk_stem_observation_record1`
517 
    FOREIGN KEY (`id` )
518 
    REFERENCES `record` (`id` )
519 
    ON DELETE CASCADE
520 
    ON UPDATE CASCADE,
521 533 
  CONSTRAINT `fk_stem_observation_individual_observation1`
522 534 
    FOREIGN KEY (`individual_observation` )
523 535 
    REFERENCES `individual_observation` (`id` )
......
527 539 
    FOREIGN KEY (`stem` )
528 540 
    REFERENCES `stem` (`id` )
529 541 
    ON DELETE CASCADE
542 
    ON UPDATE CASCADE,
543 
  CONSTRAINT `fk_stem_observation_individual_observation2`
544 
    FOREIGN KEY (`id` )
545 
    REFERENCES `individual_observation` (`id` )
546 
    ON DELETE CASCADE
530 547 
    ON UPDATE CASCADE)
531 548 
ENGINE = InnoDB
532 549 
DEFAULT CHARACTER SET = latin1
......
608 625 
-- -----------------------------------------------------
609 626 
CREATE  TABLE IF NOT EXISTS `community` (
610 627 
  `id` TEXT NOT NULL ,
628 
  `name` TEXT NOT NULL ,
629 
  `info` SET('hstore') NULL ,
611 630 
  PRIMARY KEY (`id`) ,
612 631 
  CONSTRAINT `fk_community_record1`
613 632 
    FOREIGN KEY (`id` )
......
624 643 
-- -----------------------------------------------------
625 644 
CREATE  TABLE IF NOT EXISTS `geological_context` (
626 645 
  `id` TEXT NOT NULL ,
646 
  `name` TEXT NOT NULL ,
647 
  `info` SET('hstore') NULL ,
627 648 
  PRIMARY KEY (`id`) ,
628 649 
  CONSTRAINT `fk_geological_context_record1`
629 650 
    FOREIGN KEY (`id` )
......
641 662 
CREATE  TABLE IF NOT EXISTS `place_observation` (
642 663 
  `id` TEXT NOT NULL ,
643 664 
  `place` TEXT NOT NULL ,
665 
  `elevation_m` DOUBLE NULL ,
666 
  `slope_incline_deg` DOUBLE NULL ,
667 
  `slope_direction_deg_N` DOUBLE NULL ,
644 668 
  `geological_context` TEXT NULL ,
645 669 
  `community` TEXT NULL ,
670 
  `observations` SET('hstore') NULL ,
646 671 
  INDEX `fk_place_observation_place1` (`place` ASC) ,
647 672 
  INDEX `fk_place_observation_geological_context1` (`geological_context` ASC) ,
648 673 
  INDEX `fk_place_observation_community1` (`community` ASC) ,
......
678 703 
-- -----------------------------------------------------
679 704 
CREATE  TABLE IF NOT EXISTS `soil_observation` (
680 705 
  `id` TEXT NOT NULL ,
706 
  `observations` SET('hstore') NULL ,
681 707 
  PRIMARY KEY (`id`) ,
682 708 
  CONSTRAINT `fk_soil_observation_place_observation1`
683 709 
    FOREIGN KEY (`id` )
......
694 720 
-- -----------------------------------------------------
695 721 
CREATE  TABLE IF NOT EXISTS `plot` (
696 722 
  `id` TEXT NOT NULL ,
723 
  `name` TEXT NULL ,
724 
  `area_m2` DOUBLE NULL ,
725 
  `bounding_box` TEXT NULL ,
697 726 
  PRIMARY KEY (`id`) ,
698 727 
  CONSTRAINT `fk_subplot_place1`
699 728 
    FOREIGN KEY (`id` )
......
710 739 
-- -----------------------------------------------------
711 740 
CREATE  TABLE IF NOT EXISTS `subplot` (
712 741 
  `id` TEXT NOT NULL ,
742 
  `x_m` DOUBLE NULL ,
743 
  `y_m` DOUBLE NULL ,
713 744 
  PRIMARY KEY (`id`) ,
714 745 
  CONSTRAINT `fk_subplot_plot1`
715 746 
    FOREIGN KEY (`id` )
......
726 757 
-- -----------------------------------------------------
727 758 
CREATE  TABLE IF NOT EXISTS `validatable_place` (
728 759 
  `id` TEXT NOT NULL ,
760 
  `coordinates` TEXT NOT NULL ,
729 761 
  `path` TEXT NOT NULL ,
730 
  `coordinates` TEXT NOT NULL ,
731 762 
  PRIMARY KEY (`id`) ,
732 763 
  INDEX `fk_geovalidation_place_path1` (`path` ASC) ,
733 764 
  INDEX `fk_geovalidation_coordinates1` (`coordinates` ASC) ,
......
752 783 
-- -----------------------------------------------------
753 784 
CREATE  TABLE IF NOT EXISTS `project` (
754 785 
  `id` TEXT NOT NULL ,
786 
  `name` TEXT NOT NULL ,
787 
  `info` SET('hstore') NULL ,
755 788 
  PRIMARY KEY (`id`) ,
756 789 
  CONSTRAINT `fk_project_event1`
757 790 
    FOREIGN KEY (`id` )
......
839 872 
-- -----------------------------------------------------
840 873 
CREATE  TABLE IF NOT EXISTS `stratum` (
841 874 
  `id` TEXT NOT NULL ,
875 
  `name` TEXT NOT NULL ,
876 
  `info` SET('hstore') NULL ,
842 877 
  PRIMARY KEY (`id`) ,
843 878 
  CONSTRAINT `fk_place_path_record10`
844 879 
    FOREIGN KEY (`id` )
......
962 997 
-- -----------------------------------------------------
963 998 
CREATE  TABLE IF NOT EXISTS `geovalidation` (
964 999 
  `id` TEXT NOT NULL ,
1000 
  `geovalid` TINYINT(1) NOT NULL ,
1001 
  `lat_long_domain_valid` TINYINT(1) NOT NULL ,
965 1002 
  PRIMARY KEY (`id`) ,
966 1003 
  CONSTRAINT `fk_geovalidation_validatable_place1`
967 1004 
    FOREIGN KEY (`id` )

Also available in: Unified diff