type_id for all linker tables

[laceysanderson]

It would be really good to have an optional type_id added to all linker tables in chado consistently. That way we could indicate what type of link is being made and remove a lot of ambiguity that it currently happening ;-p

Specific use cases

Contacts

Contact linkers (feature_contact, featuremap_contact, library_contact, nd_experiment_contact, project_contact, pubauthor_contact) currently do not have a type_id at all which means there is no way to indicate the role that contact has in what you are linking it to.

For example, you can see in detail in this Tripal issue that I would like to add attribution specific fields to our experiment pages such as data collector, data custodian, curator, funder, research organization, etc but currently I have no way to indicate that a specific contact is the fulfilling this role for this specific experiment. If I try to use the type_id in the contact then that assumes that contact always has that role which is unrealistic 🤪 It also assumes that a researcher only ever fulfills one role which is also unrealistic.

By having a type_id in the link I would be able to say that for this specific experiment, this contact fulfills a specific role.

Implementation

• Add a nullable type_id to all linker tables which do not already have one.
• Since it's nullable this is a backwards compatible change
• We would need to add the type_id to the unique constraint (e.g. allow one contact to fulfill multiple roles)
• Nulls in the type_id can cause duplicates (see https://github.com/GMOD/Chado/issues/139). I would mitigate this by providing a default value to these new type_ids (e.g. "connected to" RO:0002170) and by using the new PostgreSQL 15 syntax when it is available.