Revision 8598
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/VegCore/VegCore.my.sql | ||
|---|---|---|
| 446 | 446 |
-- ----------------------------------------------------- |
| 447 | 447 |
CREATE TABLE IF NOT EXISTS `collection` ( |
| 448 | 448 |
`id` TEXT NOT NULL , |
| 449 |
`name` TEXT NOT NULL , |
|
| 450 | 449 |
PRIMARY KEY (`id`) , |
| 451 | 450 |
CONSTRAINT `fk_collection_source1` |
| 452 | 451 |
FOREIGN KEY (`id` ) |
| ... | ... | |
| 459 | 458 |
|
| 460 | 459 |
|
| 461 | 460 |
-- ----------------------------------------------------- |
| 461 |
-- Table `organization` |
|
| 462 |
-- ----------------------------------------------------- |
|
| 463 |
CREATE TABLE IF NOT EXISTS `organization` ( |
|
| 464 |
`id` TEXT NOT NULL , |
|
| 465 |
`info` SET('hstore') NULL ,
|
|
| 466 |
PRIMARY KEY (`id`) , |
|
| 467 |
CONSTRAINT `fk_organization_party1` |
|
| 468 |
FOREIGN KEY (`id` ) |
|
| 469 |
REFERENCES `party` (`id` ) |
|
| 470 |
ON DELETE CASCADE |
|
| 471 |
ON UPDATE CASCADE) |
|
| 472 |
ENGINE = InnoDB |
|
| 473 |
DEFAULT CHARACTER SET = latin1 |
|
| 474 |
COLLATE = latin1_swedish_ci; |
|
| 475 |
|
|
| 476 |
|
|
| 477 |
-- ----------------------------------------------------- |
|
| 462 | 478 |
-- Table `specimen` |
| 463 | 479 |
-- ----------------------------------------------------- |
| 464 | 480 |
CREATE TABLE IF NOT EXISTS `specimen` ( |
| 465 | 481 |
`id` TEXT NOT NULL , |
| 466 |
`specimen_holder_institutions` TEXT NULL , |
|
| 482 |
`collection` TEXT NULL , |
|
| 483 |
`stamping_institution` TEXT NULL , |
|
| 467 | 484 |
`donor_institution` TEXT NULL , |
| 468 | 485 |
`owner` TEXT NULL , |
| 469 |
`stamping_institution` TEXT NULL , |
|
| 470 |
`collection` TEXT NULL , |
|
| 471 | 486 |
`barcode` TEXT NULL , |
| 472 | 487 |
`accession_number` TEXT NULL , |
| 473 | 488 |
PRIMARY KEY (`id`) , |
| 474 | 489 |
INDEX `fk_specimen_collection1` (`collection` ASC) , |
| 490 |
INDEX `fk_specimen_organization1` (`stamping_institution` ASC) , |
|
| 491 |
INDEX `fk_specimen_organization2` (`donor_institution` ASC) , |
|
| 492 |
INDEX `fk_specimen_organization3` (`owner` ASC) , |
|
| 475 | 493 |
CONSTRAINT `fk_specimen_taxon_occurrence1` |
| 476 | 494 |
FOREIGN KEY (`id` ) |
| 477 | 495 |
REFERENCES `taxon_occurrence` (`id` ) |
| ... | ... | |
| 481 | 499 |
FOREIGN KEY (`collection` ) |
| 482 | 500 |
REFERENCES `collection` (`id` ) |
| 483 | 501 |
ON DELETE CASCADE |
| 502 |
ON UPDATE CASCADE, |
|
| 503 |
CONSTRAINT `fk_specimen_organization1` |
|
| 504 |
FOREIGN KEY (`stamping_institution` ) |
|
| 505 |
REFERENCES `organization` (`id` ) |
|
| 506 |
ON DELETE CASCADE |
|
| 507 |
ON UPDATE CASCADE, |
|
| 508 |
CONSTRAINT `fk_specimen_organization2` |
|
| 509 |
FOREIGN KEY (`donor_institution` ) |
|
| 510 |
REFERENCES `organization` (`id` ) |
|
| 511 |
ON DELETE CASCADE |
|
| 512 |
ON UPDATE CASCADE, |
|
| 513 |
CONSTRAINT `fk_specimen_organization3` |
|
| 514 |
FOREIGN KEY (`owner` ) |
|
| 515 |
REFERENCES `organization` (`id` ) |
|
| 516 |
ON DELETE CASCADE |
|
| 484 | 517 |
ON UPDATE CASCADE) |
| 485 | 518 |
ENGINE = InnoDB |
| 486 | 519 |
DEFAULT CHARACTER SET = latin1 |
| ... | ... | |
| 1086 | 1119 |
|
| 1087 | 1120 |
|
| 1088 | 1121 |
-- ----------------------------------------------------- |
| 1089 |
-- Table `organization`
|
|
| 1122 |
-- Table `event_participant`
|
|
| 1090 | 1123 |
-- ----------------------------------------------------- |
| 1091 |
CREATE TABLE IF NOT EXISTS `organization` ( |
|
| 1092 |
`id` TEXT NOT NULL , |
|
| 1093 |
`info` SET('hstore') NULL ,
|
|
| 1094 |
PRIMARY KEY (`id`) , |
|
| 1095 |
CONSTRAINT `fk_organization_party1` |
|
| 1096 |
FOREIGN KEY (`id` ) |
|
| 1124 |
CREATE TABLE IF NOT EXISTS `event_participant` ( |
|
| 1125 |
`event` TEXT NOT NULL , |
|
| 1126 |
`party` TEXT NOT NULL , |
|
| 1127 |
`sort_order` INT NULL , |
|
| 1128 |
PRIMARY KEY (`event`, `party`) , |
|
| 1129 |
INDEX `fk_event_has_party_party1` (`party` ASC) , |
|
| 1130 |
INDEX `fk_event_has_party_event1` (`event` ASC) , |
|
| 1131 |
CONSTRAINT `fk_event_has_party_event1` |
|
| 1132 |
FOREIGN KEY (`event` ) |
|
| 1133 |
REFERENCES `event` (`id` ) |
|
| 1134 |
ON DELETE CASCADE |
|
| 1135 |
ON UPDATE CASCADE, |
|
| 1136 |
CONSTRAINT `fk_event_has_party_party1` |
|
| 1137 |
FOREIGN KEY (`party` ) |
|
| 1097 | 1138 |
REFERENCES `party` (`id` ) |
| 1098 | 1139 |
ON DELETE CASCADE |
| 1099 | 1140 |
ON UPDATE CASCADE) |
| ... | ... | |
| 1103 | 1144 |
|
| 1104 | 1145 |
|
| 1105 | 1146 |
-- ----------------------------------------------------- |
| 1106 |
-- Table `event_participant`
|
|
| 1147 |
-- Table `specimen_holder_institution`
|
|
| 1107 | 1148 |
-- ----------------------------------------------------- |
| 1108 |
CREATE TABLE IF NOT EXISTS `event_participant` (
|
|
| 1109 |
`event_id` TEXT NOT NULL ,
|
|
| 1110 |
`party_id` TEXT NOT NULL ,
|
|
| 1149 |
CREATE TABLE IF NOT EXISTS `specimen_holder_institution` (
|
|
| 1150 |
`specimen` TEXT NOT NULL ,
|
|
| 1151 |
`institution` TEXT NOT NULL ,
|
|
| 1111 | 1152 |
`sort_order` INT NULL , |
| 1112 |
PRIMARY KEY (`event_id`, `party_id`) ,
|
|
| 1113 |
INDEX `fk_event_has_party_party1` (`party_id` ASC) ,
|
|
| 1114 |
INDEX `fk_event_has_party_event1` (`event_id` ASC) ,
|
|
| 1115 |
CONSTRAINT `fk_event_has_party_event1`
|
|
| 1116 |
FOREIGN KEY (`event_id` )
|
|
| 1117 |
REFERENCES `event` (`id` )
|
|
| 1153 |
PRIMARY KEY (`specimen`, `institution`) ,
|
|
| 1154 |
INDEX `fk_specimen_has_organization_organization1` (`institution` ASC) ,
|
|
| 1155 |
INDEX `fk_specimen_has_organization_specimen1` (`specimen` ASC) ,
|
|
| 1156 |
CONSTRAINT `fk_specimen_has_organization_specimen1`
|
|
| 1157 |
FOREIGN KEY (`specimen` )
|
|
| 1158 |
REFERENCES `specimen` (`id` )
|
|
| 1118 | 1159 |
ON DELETE CASCADE |
| 1119 | 1160 |
ON UPDATE CASCADE, |
| 1120 |
CONSTRAINT `fk_event_has_party_party1`
|
|
| 1121 |
FOREIGN KEY (`party_id` )
|
|
| 1122 |
REFERENCES `party` (`id` )
|
|
| 1161 |
CONSTRAINT `fk_specimen_has_organization_organization1`
|
|
| 1162 |
FOREIGN KEY (`institution` )
|
|
| 1163 |
REFERENCES `organization` (`id` )
|
|
| 1123 | 1164 |
ON DELETE CASCADE |
| 1124 | 1165 |
ON UPDATE CASCADE) |
| 1125 | 1166 |
ENGINE = InnoDB |
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: specimen: replaced specimen_holder_institutions with specimen_holder_institution many:many table. added fkeys for the various institution fields. collection: removed name because this is already a required field in source. event_participant: removed *_id suffixes from fields.