intertaxon relationships (not taxonomic)

[mitcheka]

We'd like to include information on our taxon profile pages about relationships with other taxa that are not taxonomic relationships, such as host/parasite, pollinator/plant, larval host plant/insect, predator/prey, commonly associated taxa, look-alike taxa, and so on. This would require at least one table to capture the data. It could be structured similarly to omoccurassociations. At a minimum, the fields needed would be: two taxon identifier fields, two more fields that identify their roles in the relationship, maybe another for the category of relationship, as well as a text field for public display that describes or qualifies the relationship. Links to supporting occurrences or references, and maybe internal comments could also be useful. Is this of interest to other portals?

[mitcheka]

To help people better understand what I'm proposing, here is a possible structure (in plain English) below. This is analogous to the existing symbiota table, omoccurassociations, and both are in the dwc ResourceRelationship class, https://dwc.tdwg.org/terms/#resourcerelationship.

Table: taxaassociations
Fields:
taxonAssocID - auto increment primary key, dwc: resourceRelationshipID
tid (not null) - taxon identifier for the subject of the relationship, example: tid of the parasite taxon, dwc: resourceID
tidAssociate - taxon identifier for the object of the relationship, example: tid of the host taxon, dwc: relatedResourceID
relationship - the relationship of the subject to the object, example: parasite of, dwc: relationshipOfResource
relationshipID - dwc: relationshipOfResourceID
relationshipAccordingTo: the source establishing the relationship, dwc: relationshipAccordingTo
subType -
identifier - dwc: relatedResourceID (object identifier), if the related taxon does not have a local tid, this is the identifier used elsewhere
resourceUrl - link to object resource
verbatimSciname - object scientific name
notes - dwc: relationshipRemarks
sourceIdentifier - dwc: resourceRelationshipID (association identifier), if association was defined externally
createdUid
modifiedTimestamp
modifiedUid
initialTimestamp

Unique key on: tid, tidAssociate, relationship,
unique key on: tid, relationship, resourceurl (external)

The field, relationshipType, would be a foreign key on the following table:
Table: taxainterrelationshiptype
Fields:
taxainterrelationtypeid (not null) - an auto-number
relationshipText (not null) - the description of the relationship of tid to related tid, such as "parasite of"
relationshipInverseText - the description of the relationship of relatedtid to tid, such as "host of"
relationshipDetailHeader - the header for the relationshipdetail, such as "frequency of parasitism"
timestamp(s)
uid

Links to supporting occurrences could be a separate table. Relationship references could also be a separate table.