Table relationships in EML

database
eml
Tags: #<Tag:0x00007f57fae4c218> #<Tag:0x00007f57fae4b8e0>

#1

How do I describe a foreign key relationship in EML metadata?

I don’t see guidance on this within the EML package, and I’m not even sure it’s supported in EML. I thought maybe the enumeratedDomain > entityCodeList > entityReference nesting of tags might do it, but that’s probably designed for ENUM tables and shouldn’t be coopted for foreign key constraints.

If there is a package meant to export a database schema into an EML (or any other standard) metadata, it would surely have to have answered this question … but I can find no such tool :face_with_raised_eyebrow:


#2

thanks for your question @itcarroll

any thoughts @maelle or @cboettig ?


#3

Good question. There’s a notion of foreignKey in the constraint element (which is a child element of an EML dataTable):


#4

(Constraint is also part of the storedProcedure module in a dataTable, which is I believe geared towards relational databases. The constraint module appears to focus on relationships between tables, while the attributeList focuses on relationships in a table. Here’s a canonical example from the EML team for metadata markup of a RDBMS: https://portal.edirepository.org/nis/metadataviewer?packageid=knb-lter-mcr.4.33&contentType=application/xml

Being able to generate these from the RDBM schema seems like a reasonable feature for EML package, not sure what format that schema would be in. Would be great if you wanted to open a feature request for that in https://github.com/ropensci/EML/issues ideally with some example of what the function API should look like.


#5

Fantastic. Thank you! I will work on a useful feature request to continue discussion, and could put some work in too.