Revision 8581
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/VegCore/VegCore.my.sql | ||
|---|---|---|
| 177 | 177 |
|
| 178 | 178 |
|
| 179 | 179 |
-- ----------------------------------------------------- |
| 180 |
-- Table `party` |
|
| 181 |
-- ----------------------------------------------------- |
|
| 182 |
CREATE TABLE IF NOT EXISTS `party` ( |
|
| 183 |
`id` TEXT NOT NULL , |
|
| 184 |
`info` SET('hstore') NULL ,
|
|
| 185 |
PRIMARY KEY (`id`) , |
|
| 186 |
CONSTRAINT `fk_collection_source10` |
|
| 187 |
FOREIGN KEY (`id` ) |
|
| 188 |
REFERENCES `source` (`id` ) |
|
| 189 |
ON DELETE CASCADE |
|
| 190 |
ON UPDATE CASCADE) |
|
| 191 |
ENGINE = InnoDB |
|
| 192 |
DEFAULT CHARACTER SET = latin1 |
|
| 193 |
COLLATE = latin1_swedish_ci; |
|
| 194 |
|
|
| 195 |
|
|
| 196 |
-- ----------------------------------------------------- |
|
| 197 |
-- Table `taxon_determination` |
|
| 198 |
-- ----------------------------------------------------- |
|
| 199 |
CREATE TABLE IF NOT EXISTS `taxon_determination` ( |
|
| 200 |
`id` TEXT NOT NULL , |
|
| 201 |
`taxon_occurrence` TEXT NOT NULL , |
|
| 202 |
`taxon_assertion` TEXT NOT NULL , |
|
| 203 |
`identified_by` TEXT NULL , |
|
| 204 |
`identified_date` DATE NULL , |
|
| 205 |
`fit_info` SET('hstore') NULL ,
|
|
| 206 |
INDEX `fk_taxon_occurrence_has_qualified_taxon1` (`taxon_assertion` ASC) , |
|
| 207 |
INDEX `fk_taxon_occurrence_has_qualified_taxon_occurrence1` (`taxon_occurrence` ASC) , |
|
| 208 |
PRIMARY KEY (`id`) , |
|
| 209 |
INDEX `fk_taxon_determination_party1` (`identified_by` ASC) , |
|
| 210 |
UNIQUE INDEX `taxon_determination_unique` (`taxon_occurrence` ASC, `taxon_assertion` ASC, `identified_by` ASC, `identified_date` ASC) , |
|
| 211 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon_occurrence1` |
|
| 212 |
FOREIGN KEY (`taxon_occurrence` ) |
|
| 213 |
REFERENCES `taxon_occurrence` (`id` ) |
|
| 214 |
ON DELETE CASCADE |
|
| 215 |
ON UPDATE CASCADE, |
|
| 216 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` |
|
| 217 |
FOREIGN KEY (`taxon_assertion` ) |
|
| 218 |
REFERENCES `taxon_assertion` (`id` ) |
|
| 219 |
ON DELETE CASCADE |
|
| 220 |
ON UPDATE CASCADE, |
|
| 221 |
CONSTRAINT `fk_taxon_determination_record1` |
|
| 222 |
FOREIGN KEY (`id` ) |
|
| 223 |
REFERENCES `record` (`id` ) |
|
| 224 |
ON DELETE CASCADE |
|
| 225 |
ON UPDATE CASCADE, |
|
| 226 |
CONSTRAINT `fk_taxon_determination_party1` |
|
| 227 |
FOREIGN KEY (`identified_by` ) |
|
| 228 |
REFERENCES `party` (`id` ) |
|
| 229 |
ON DELETE CASCADE |
|
| 230 |
ON UPDATE CASCADE) |
|
| 231 |
ENGINE = InnoDB |
|
| 232 |
DEFAULT CHARACTER SET = latin1 |
|
| 233 |
COLLATE = latin1_swedish_ci; |
|
| 234 |
|
|
| 235 |
|
|
| 236 |
-- ----------------------------------------------------- |
|
| 237 | 180 |
-- Table `coordinates` |
| 238 | 181 |
-- ----------------------------------------------------- |
| 239 | 182 |
CREATE TABLE IF NOT EXISTS `coordinates` ( |
| ... | ... | |
| 356 | 299 |
|
| 357 | 300 |
|
| 358 | 301 |
-- ----------------------------------------------------- |
| 302 |
-- Table `taxon_observation` |
|
| 303 |
-- ----------------------------------------------------- |
|
| 304 |
CREATE TABLE IF NOT EXISTS `taxon_observation` ( |
|
| 305 |
`id` TEXT NOT NULL , |
|
| 306 |
`taxon_occurrence` TEXT NOT NULL , |
|
| 307 |
`voucher` TEXT NULL , |
|
| 308 |
`growth_form` TEXT NULL , |
|
| 309 |
`cultivated` TINYINT(1) NULL , |
|
| 310 |
`traits` SET('hstore') NULL ,
|
|
| 311 |
PRIMARY KEY (`id`) , |
|
| 312 |
INDEX `fk_taxon_observation_taxon_occurrence2` (`taxon_occurrence` ASC) , |
|
| 313 |
INDEX `fk_taxon_observation_specimen1` (`voucher` ASC) , |
|
| 314 |
CONSTRAINT `fk_taxon_observation_event1` |
|
| 315 |
FOREIGN KEY (`id` ) |
|
| 316 |
REFERENCES `event` (`id` ) |
|
| 317 |
ON DELETE CASCADE |
|
| 318 |
ON UPDATE CASCADE, |
|
| 319 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence2` |
|
| 320 |
FOREIGN KEY (`taxon_occurrence` ) |
|
| 321 |
REFERENCES `taxon_occurrence` (`id` ) |
|
| 322 |
ON DELETE CASCADE |
|
| 323 |
ON UPDATE CASCADE, |
|
| 324 |
CONSTRAINT `fk_taxon_observation_specimen1` |
|
| 325 |
FOREIGN KEY (`voucher` ) |
|
| 326 |
REFERENCES `specimen` (`id` ) |
|
| 327 |
ON DELETE CASCADE |
|
| 328 |
ON UPDATE CASCADE) |
|
| 329 |
ENGINE = InnoDB |
|
| 330 |
DEFAULT CHARACTER SET = latin1 |
|
| 331 |
COLLATE = latin1_swedish_ci; |
|
| 332 |
|
|
| 333 |
|
|
| 334 |
-- ----------------------------------------------------- |
|
| 335 |
-- Table `party` |
|
| 336 |
-- ----------------------------------------------------- |
|
| 337 |
CREATE TABLE IF NOT EXISTS `party` ( |
|
| 338 |
`id` TEXT NOT NULL , |
|
| 339 |
`info` SET('hstore') NULL ,
|
|
| 340 |
PRIMARY KEY (`id`) , |
|
| 341 |
CONSTRAINT `fk_collection_source10` |
|
| 342 |
FOREIGN KEY (`id` ) |
|
| 343 |
REFERENCES `source` (`id` ) |
|
| 344 |
ON DELETE CASCADE |
|
| 345 |
ON UPDATE CASCADE) |
|
| 346 |
ENGINE = InnoDB |
|
| 347 |
DEFAULT CHARACTER SET = latin1 |
|
| 348 |
COLLATE = latin1_swedish_ci; |
|
| 349 |
|
|
| 350 |
|
|
| 351 |
-- ----------------------------------------------------- |
|
| 352 |
-- Table `taxon_determination` |
|
| 353 |
-- ----------------------------------------------------- |
|
| 354 |
CREATE TABLE IF NOT EXISTS `taxon_determination` ( |
|
| 355 |
`id` TEXT NOT NULL , |
|
| 356 |
`taxon_assertion` TEXT NOT NULL , |
|
| 357 |
`identified_by` TEXT NULL , |
|
| 358 |
`fit_info` SET('hstore') NULL ,
|
|
| 359 |
INDEX `fk_taxon_occurrence_has_qualified_taxon1` (`taxon_assertion` ASC) , |
|
| 360 |
PRIMARY KEY (`id`) , |
|
| 361 |
INDEX `fk_taxon_determination_party1` (`identified_by` ASC) , |
|
| 362 |
UNIQUE INDEX `taxon_determination_unique` (`taxon_assertion` ASC, `identified_by` ASC) , |
|
| 363 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` |
|
| 364 |
FOREIGN KEY (`taxon_assertion` ) |
|
| 365 |
REFERENCES `taxon_assertion` (`id` ) |
|
| 366 |
ON DELETE CASCADE |
|
| 367 |
ON UPDATE CASCADE, |
|
| 368 |
CONSTRAINT `fk_taxon_determination_record1` |
|
| 369 |
FOREIGN KEY () |
|
| 370 |
REFERENCES `taxon_observation` () |
|
| 371 |
ON DELETE CASCADE |
|
| 372 |
ON UPDATE CASCADE, |
|
| 373 |
CONSTRAINT `fk_taxon_determination_party1` |
|
| 374 |
FOREIGN KEY (`identified_by` ) |
|
| 375 |
REFERENCES `party` (`id` ) |
|
| 376 |
ON DELETE CASCADE |
|
| 377 |
ON UPDATE CASCADE) |
|
| 378 |
ENGINE = InnoDB |
|
| 379 |
DEFAULT CHARACTER SET = latin1 |
|
| 380 |
COLLATE = latin1_swedish_ci; |
|
| 381 |
|
|
| 382 |
|
|
| 383 |
-- ----------------------------------------------------- |
|
| 359 | 384 |
-- Table `taxon_occurrence` |
| 360 | 385 |
-- ----------------------------------------------------- |
| 361 | 386 |
CREATE TABLE IF NOT EXISTS `taxon_occurrence` ( |
| 362 | 387 |
`id` TEXT NOT NULL , |
| 363 | 388 |
`collector` TEXT NULL , |
| 389 |
`collection_event` TEXT NULL , |
|
| 364 | 390 |
`collector_number` TEXT NULL , |
| 365 | 391 |
`current_determination` TEXT NULL , |
| 366 | 392 |
`original_determination` TEXT NULL , |
| 367 |
`growth_form` TEXT NULL , |
|
| 368 |
`cultivated` TINYINT(1) NULL , |
|
| 369 |
`traits` SET('hstore') NULL ,
|
|
| 370 | 393 |
PRIMARY KEY (`id`) , |
| 371 | 394 |
INDEX `fk_taxon_occurrence_taxon_determination1` (`original_determination` ASC) , |
| 372 | 395 |
INDEX `fk_taxon_occurrence_taxon_determination2` (`current_determination` ASC) , |
| 373 | 396 |
INDEX `fk_taxon_occurrence_party1` (`collector` ASC) , |
| 397 |
INDEX `fk_taxon_occurrence_event2` (`collection_event` ASC) , |
|
| 374 | 398 |
CONSTRAINT `fk_taxon_occurrence_taxon_determination1` |
| 375 | 399 |
FOREIGN KEY (`original_determination` ) |
| 376 | 400 |
REFERENCES `taxon_determination` (`id` ) |
| ... | ... | |
| 382 | 406 |
ON DELETE CASCADE |
| 383 | 407 |
ON UPDATE CASCADE, |
| 384 | 408 |
CONSTRAINT `fk_taxon_occurrence_event1` |
| 385 |
FOREIGN KEY (`id` )
|
|
| 386 |
REFERENCES `event` (`id` )
|
|
| 409 |
FOREIGN KEY () |
|
| 410 |
REFERENCES `record` ()
|
|
| 387 | 411 |
ON DELETE CASCADE |
| 388 | 412 |
ON UPDATE CASCADE, |
| 389 | 413 |
CONSTRAINT `fk_taxon_occurrence_party1` |
| 390 | 414 |
FOREIGN KEY (`collector` ) |
| 391 | 415 |
REFERENCES `party` (`id` ) |
| 392 | 416 |
ON DELETE CASCADE |
| 417 |
ON UPDATE CASCADE, |
|
| 418 |
CONSTRAINT `fk_taxon_occurrence_event2` |
|
| 419 |
FOREIGN KEY (`collection_event` ) |
|
| 420 |
REFERENCES `event` (`id` ) |
|
| 421 |
ON DELETE CASCADE |
|
| 393 | 422 |
ON UPDATE CASCADE) |
| 394 | 423 |
ENGINE = InnoDB |
| 395 | 424 |
DEFAULT CHARACTER SET = latin1 |
| ... | ... | |
| 443 | 472 |
|
| 444 | 473 |
|
| 445 | 474 |
-- ----------------------------------------------------- |
| 446 |
-- Table `voucher` |
|
| 447 |
-- ----------------------------------------------------- |
|
| 448 |
CREATE TABLE IF NOT EXISTS `voucher` ( |
|
| 449 |
`id` TEXT NOT NULL , |
|
| 450 |
`taxon_occurrence` TEXT NOT NULL , |
|
| 451 |
`specimen` TEXT NOT NULL , |
|
| 452 |
PRIMARY KEY (`id`) , |
|
| 453 |
INDEX `fk_voucher_taxon_occurrence1` (`taxon_occurrence` ASC) , |
|
| 454 |
INDEX `fk_voucher_specimen1` (`specimen` ASC) , |
|
| 455 |
UNIQUE INDEX `voucher_unique` (`taxon_occurrence` ASC, `specimen` ASC) , |
|
| 456 |
CONSTRAINT `fk_voucher_record1` |
|
| 457 |
FOREIGN KEY (`id` ) |
|
| 458 |
REFERENCES `record` (`id` ) |
|
| 459 |
ON DELETE CASCADE |
|
| 460 |
ON UPDATE CASCADE, |
|
| 461 |
CONSTRAINT `fk_voucher_taxon_occurrence1` |
|
| 462 |
FOREIGN KEY (`taxon_occurrence` ) |
|
| 463 |
REFERENCES `taxon_occurrence` (`id` ) |
|
| 464 |
ON DELETE CASCADE |
|
| 465 |
ON UPDATE CASCADE, |
|
| 466 |
CONSTRAINT `fk_voucher_specimen1` |
|
| 467 |
FOREIGN KEY (`specimen` ) |
|
| 468 |
REFERENCES `specimen` (`id` ) |
|
| 469 |
ON DELETE CASCADE |
|
| 470 |
ON UPDATE CASCADE) |
|
| 471 |
ENGINE = InnoDB |
|
| 472 |
DEFAULT CHARACTER SET = latin1 |
|
| 473 |
COLLATE = latin1_swedish_ci; |
|
| 474 |
|
|
| 475 |
|
|
| 476 |
-- ----------------------------------------------------- |
|
| 477 | 475 |
-- Table `individual` |
| 478 | 476 |
-- ----------------------------------------------------- |
| 479 | 477 |
CREATE TABLE IF NOT EXISTS `individual` ( |
| ... | ... | |
| 482 | 480 |
`code` TEXT NULL , |
| 483 | 481 |
PRIMARY KEY (`id`) , |
| 484 | 482 |
CONSTRAINT `fk_individual_record1` |
| 485 |
FOREIGN KEY (`id` )
|
|
| 486 |
REFERENCES `record` (`id` )
|
|
| 483 |
FOREIGN KEY () |
|
| 484 |
REFERENCES `taxon_occurrence` ()
|
|
| 487 | 485 |
ON DELETE CASCADE |
| 488 | 486 |
ON UPDATE CASCADE) |
| 489 | 487 |
ENGINE = InnoDB |
| ... | ... | |
| 501 | 499 |
PRIMARY KEY (`id`) , |
| 502 | 500 |
INDEX `fk_individual_observation_individual1` (`individual` ASC) , |
| 503 | 501 |
CONSTRAINT `fk_individual_observation_taxon_occurrence1` |
| 504 |
FOREIGN KEY (`id` )
|
|
| 505 |
REFERENCES `taxon_occurrence` (`id` )
|
|
| 502 |
FOREIGN KEY () |
|
| 503 |
REFERENCES `taxon_observation` ()
|
|
| 506 | 504 |
ON DELETE CASCADE |
| 507 | 505 |
ON UPDATE CASCADE, |
| 508 | 506 |
CONSTRAINT `fk_individual_observation_individual1` |
| ... | ... | |
| 577 | 575 |
`id` TEXT NOT NULL , |
| 578 | 576 |
PRIMARY KEY (`id`) , |
| 579 | 577 |
CONSTRAINT `fk_aggregate_observation_taxon_occurrence1` |
| 580 |
FOREIGN KEY (`id` )
|
|
| 581 |
REFERENCES `taxon_occurrence` (`id` )
|
|
| 578 |
FOREIGN KEY () |
|
| 579 |
REFERENCES `taxon_observation` ()
|
|
| 582 | 580 |
ON DELETE CASCADE |
| 583 | 581 |
ON UPDATE CASCADE) |
| 584 | 582 |
ENGINE = InnoDB |
| ... | ... | |
| 593 | 591 |
`id` TEXT NOT NULL , |
| 594 | 592 |
PRIMARY KEY (`id`) , |
| 595 | 593 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence1` |
| 596 |
FOREIGN KEY (`id` )
|
|
| 597 |
REFERENCES `taxon_occurrence` (`id` )
|
|
| 594 |
FOREIGN KEY () |
|
| 595 |
REFERENCES `taxon_observation` ()
|
|
| 598 | 596 |
ON DELETE CASCADE |
| 599 | 597 |
ON UPDATE CASCADE) |
| 600 | 598 |
ENGINE = InnoDB |
| ... | ... | |
| 1050 | 1048 |
COLLATE = latin1_swedish_ci; |
| 1051 | 1049 |
|
| 1052 | 1050 |
|
| 1051 |
-- ----------------------------------------------------- |
|
| 1052 |
-- Table `specimen_observation` |
|
| 1053 |
-- ----------------------------------------------------- |
|
| 1054 |
CREATE TABLE IF NOT EXISTS `specimen_observation` ( |
|
| 1055 |
`id` TEXT NOT NULL , |
|
| 1056 |
`specimen` TEXT NOT NULL , |
|
| 1057 |
PRIMARY KEY (`id`) , |
|
| 1058 |
INDEX `fk_specimen_observation_specimen1` (`specimen` ASC) , |
|
| 1059 |
CONSTRAINT `fk_specimen_taxon_occurrence10` |
|
| 1060 |
FOREIGN KEY () |
|
| 1061 |
REFERENCES `taxon_observation` () |
|
| 1062 |
ON DELETE CASCADE |
|
| 1063 |
ON UPDATE CASCADE, |
|
| 1064 |
CONSTRAINT `fk_specimen_observation_specimen1` |
|
| 1065 |
FOREIGN KEY (`specimen` ) |
|
| 1066 |
REFERENCES `specimen` (`id` ) |
|
| 1067 |
ON DELETE CASCADE |
|
| 1068 |
ON UPDATE CASCADE) |
|
| 1069 |
ENGINE = InnoDB |
|
| 1070 |
DEFAULT CHARACTER SET = latin1 |
|
| 1071 |
COLLATE = latin1_swedish_ci; |
|
| 1053 | 1072 |
|
| 1073 |
|
|
| 1074 |
|
|
| 1054 | 1075 |
SET SQL_MODE=@OLD_SQL_MODE; |
| 1055 | 1076 |
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; |
| 1056 | 1077 |
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; |
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: split taxon_occurrence into taxon_occurrence and taxon_observation, because a taxon_*occurrence* is actually the thing on which a taxon_*observation* is made, not the observation itself. rearranged tables significantly to put observations next to the things they observe, and to make the ERD less wide so it will fit on a small screen without as much scaling (allowing a bigger font size when the ERD is shrunk to fit the screen).