Revision 10455
Added by Aaron Marcuse-Kubitza almost 11 years ago
| VegCore.my.sql | ||
|---|---|---|
| 169 | 169 |
KEY `fk_event1_idx` (`parent`), |
| 170 | 170 |
KEY `fk_event_method1_idx` (`method`), |
| 171 | 171 |
KEY `fk_event_party_list1_idx` (`participants`), |
| 172 |
CONSTRAINT `fk_event_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 173 |
CONSTRAINT `fk_event_place1` FOREIGN KEY (`place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 172 |
CONSTRAINT `fk_event_party_list1` FOREIGN KEY (`participants`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 174 | 173 |
CONSTRAINT `fk_event1` FOREIGN KEY (`parent`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 175 | 174 |
CONSTRAINT `fk_event_method1` FOREIGN KEY (`method`) REFERENCES `method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 176 |
CONSTRAINT `fk_event_party_list1` FOREIGN KEY (`participants`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 175 |
CONSTRAINT `fk_event_place1` FOREIGN KEY (`place`) REFERENCES `place` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 176 |
CONSTRAINT `fk_event_record1` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 177 | 177 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='"an action that occurs at a place and during a period of time" ("DwC":http://rs.tdwg.org/dwc/terms/#Event)';
|
| 178 | 178 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 179 | 179 |
|
| ... | ... | |
| 185 | 185 |
/*!40000 ALTER TABLE `event` ENABLE KEYS */; |
| 186 | 186 |
|
| 187 | 187 |
-- |
| 188 |
-- Table structure for table `event_participant` |
|
| 189 |
-- |
|
| 190 |
|
|
| 191 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 192 |
/*!40101 SET character_set_client = utf8 */; |
|
| 193 |
CREATE TABLE `event_participant` ( |
|
| 194 |
`event` varbinary(767) NOT NULL, |
|
| 195 |
`party` varbinary(767) NOT NULL, |
|
| 196 |
`sort_order` int(11) DEFAULT NULL, |
|
| 197 |
PRIMARY KEY (`event`,`party`), |
|
| 198 |
KEY `fk_event_has_party_party1_idx` (`party`), |
|
| 199 |
KEY `fk_event_has_party_event1_idx` (`event`), |
|
| 200 |
CONSTRAINT `fk_event_has_party_event1` FOREIGN KEY (`event`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 201 |
CONSTRAINT `fk_event_has_party_party1` FOREIGN KEY (`party`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 202 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
|
| 203 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 204 |
|
|
| 205 |
-- |
|
| 206 |
-- Dumping data for table `event_participant` |
|
| 207 |
-- |
|
| 208 |
|
|
| 209 |
/*!40000 ALTER TABLE `event_participant` DISABLE KEYS */; |
|
| 210 |
/*!40000 ALTER TABLE `event_participant` ENABLE KEYS */; |
|
| 211 |
|
|
| 212 |
-- |
|
| 213 | 188 |
-- Table structure for table `geological_context` |
| 214 | 189 |
-- |
| 215 | 190 |
|
| ... | ... | |
| 289 | 264 |
`traits` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
| 290 | 265 |
PRIMARY KEY (`id`), |
| 291 | 266 |
KEY `fk_individual_observation_individual1_idx` (`individual`), |
| 292 |
CONSTRAINT `fk_individual_observation_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 293 |
CONSTRAINT `fk_individual_observation_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 267 |
CONSTRAINT `fk_individual_observation_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 268 |
CONSTRAINT `fk_individual_observation_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 294 | 269 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An observation of an [[VegCore#Individual|Individual]]'; |
| 295 | 270 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 296 | 271 |
|
| ... | ... | |
| 383 | 358 |
`info` set('hstore') COLLATE utf8_bin DEFAULT NULL,
|
| 384 | 359 |
PRIMARY KEY (`id`), |
| 385 | 360 |
KEY `fk_party_organization1_idx` (`organization`), |
| 386 |
CONSTRAINT `fk_collection_source10` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 387 |
CONSTRAINT `fk_party_organization1` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 361 |
CONSTRAINT `fk_party_organization1` FOREIGN KEY (`organization`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
|
|
| 362 |
CONSTRAINT `fk_collection_source10` FOREIGN KEY (`id`) REFERENCES `record` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 388 | 363 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
| 389 | 364 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 390 | 365 |
|
| ... | ... | |
| 728 | 703 |
KEY `fk_specimen_collection2_idx` (`current_collection`), |
| 729 | 704 |
KEY `fk_specimen_organization3_idx` (`owner_collection`), |
| 730 | 705 |
KEY `fk_specimen_party_list1_idx` (`specimenholder_institutions`), |
| 731 |
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 732 |
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 733 | 706 |
CONSTRAINT `fk_specimen_organization3` FOREIGN KEY (`owner_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 734 | 707 |
CONSTRAINT `fk_specimen_taxon_observation1` FOREIGN KEY (`collection_event`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 735 | 708 |
CONSTRAINT `fk_specimen_individual1` FOREIGN KEY (`individual`) REFERENCES `individual` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 736 | 709 |
CONSTRAINT `fk_specimen_collection2` FOREIGN KEY (`current_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 737 |
CONSTRAINT `fk_specimen_party_list1` FOREIGN KEY (`specimenholder_institutions`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 710 |
CONSTRAINT `fk_specimen_party_list1` FOREIGN KEY (`specimenholder_institutions`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 711 |
CONSTRAINT `fk_specimen_collection1` FOREIGN KEY (`orig_collection`) REFERENCES `collection` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 712 |
CONSTRAINT `fk_specimen_taxon_occurrence1` FOREIGN KEY (`id`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 738 | 713 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='A "part of a plant" ("Wikipedia":http://en.wikipedia.org/wiki/Specimen) which was collected from it';
|
| 739 | 714 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 740 | 715 |
|
| ... | ... | |
| 770 | 745 |
/*!40000 ALTER TABLE `specimen_observation` ENABLE KEYS */; |
| 771 | 746 |
|
| 772 | 747 |
-- |
| 773 |
-- Table structure for table `specimenholder_institution` |
|
| 774 |
-- |
|
| 775 |
|
|
| 776 |
/*!40101 SET @saved_cs_client = @@character_set_client */; |
|
| 777 |
/*!40101 SET character_set_client = utf8 */; |
|
| 778 |
CREATE TABLE `specimenholder_institution` ( |
|
| 779 |
`specimen` varbinary(767) NOT NULL, |
|
| 780 |
`institution` varbinary(767) NOT NULL, |
|
| 781 |
`sort_order` int(11) DEFAULT NULL, |
|
| 782 |
PRIMARY KEY (`specimen`,`institution`), |
|
| 783 |
KEY `fk_specimen_has_organization_organization1_idx` (`institution`), |
|
| 784 |
KEY `fk_specimen_has_organization_specimen1_idx` (`specimen`), |
|
| 785 |
CONSTRAINT `fk_specimen_has_organization_specimen1` FOREIGN KEY (`specimen`) REFERENCES `specimen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 786 |
CONSTRAINT `fk_specimen_has_organization_organization1` FOREIGN KEY (`institution`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE |
|
| 787 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
|
| 788 |
/*!40101 SET character_set_client = @saved_cs_client */; |
|
| 789 |
|
|
| 790 |
-- |
|
| 791 |
-- Dumping data for table `specimenholder_institution` |
|
| 792 |
-- |
|
| 793 |
|
|
| 794 |
/*!40000 ALTER TABLE `specimenholder_institution` DISABLE KEYS */; |
|
| 795 |
/*!40000 ALTER TABLE `specimenholder_institution` ENABLE KEYS */; |
|
| 796 |
|
|
| 797 |
-- |
|
| 798 | 748 |
-- Table structure for table `stem` |
| 799 | 749 |
-- |
| 800 | 750 |
|
| ... | ... | |
| 961 | 911 |
PRIMARY KEY (`id`), |
| 962 | 912 |
UNIQUE KEY `taxon_determination_unique` (`taxon_assertion`,`identified_by`), |
| 963 | 913 |
KEY `fk_taxon_occurrence_has_qualified_taxon1_idx` (`taxon_assertion`), |
| 964 |
KEY `fk_taxon_determination_party1_idx` (`identified_by`), |
|
| 914 |
KEY `fk_taxon_determination_party_list1_idx` (`identified_by`),
|
|
| 965 | 915 |
CONSTRAINT `fk_taxon_occurrence_has_qualified_taxon1` FOREIGN KEY (`taxon_assertion`) REFERENCES `taxon_assertion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 966 | 916 |
CONSTRAINT `fk_taxon_determination_record1` FOREIGN KEY (`id`) REFERENCES `taxon_observation` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 967 |
CONSTRAINT `fk_taxon_determination_party1` FOREIGN KEY (`identified_by`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 917 |
CONSTRAINT `fk_taxon_determination_party_list1` FOREIGN KEY (`identified_by`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 968 | 918 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='An assertion that a [[VegCore#TaxonOccurrence|TaxonOccurrence]] is a particular [[VegCore#Taxon|Taxon]]'; |
| 969 | 919 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 970 | 920 |
|
| ... | ... | |
| 1012 | 962 |
CREATE TABLE `taxon_observation` ( |
| 1013 | 963 |
`id` varbinary(767) NOT NULL, |
| 1014 | 964 |
`taxon_occurrence` varbinary(767) NOT NULL, |
| 1015 |
`collector` varbinary(767) DEFAULT NULL, |
|
| 965 |
`collectors` varbinary(767) DEFAULT NULL,
|
|
| 1016 | 966 |
`collector_number` varbinary(767) DEFAULT NULL, |
| 1017 | 967 |
`voucher` varbinary(767) DEFAULT NULL, |
| 1018 | 968 |
`growth_form` varbinary(767) DEFAULT NULL, |
| ... | ... | |
| 1021 | 971 |
PRIMARY KEY (`id`), |
| 1022 | 972 |
KEY `fk_taxon_observation_taxon_occurrence2_idx` (`taxon_occurrence`), |
| 1023 | 973 |
KEY `fk_taxon_observation_specimen1_idx` (`voucher`), |
| 1024 |
KEY `fk_taxon_observation_party1_idx` (`collector`), |
|
| 974 |
KEY `fk_taxon_observation_party_list1_idx` (`collectors`), |
|
| 975 |
CONSTRAINT `fk_taxon_observation_party_list1` FOREIGN KEY (`collectors`) REFERENCES `party_list` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 1025 | 976 |
CONSTRAINT `fk_taxon_observation_event1` FOREIGN KEY (`id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1026 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence2` FOREIGN KEY (`taxon_occurrence`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
|
| 1027 | 977 |
CONSTRAINT `fk_taxon_observation_specimen1` FOREIGN KEY (`voucher`) REFERENCES `specimen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
| 1028 |
CONSTRAINT `fk_taxon_observation_party1` FOREIGN KEY (`collector`) REFERENCES `party` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 978 |
CONSTRAINT `fk_taxon_observation_taxon_occurrence2` FOREIGN KEY (`taxon_occurrence`) REFERENCES `taxon_occurrence` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
|
|
| 1029 | 979 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; |
| 1030 | 980 |
/*!40101 SET character_set_client = @saved_cs_client */; |
| 1031 | 981 |
|
Also available in: Unified diff
schemas/VegCore/VegCore.ERD.mwb: collector, identified_by: allow multiple parties for these fields, using the new party_list array table