Capture table relationships

[TomAugspurger]

When working with collections of tables that are essentially a database export (e.g. https://github.com/microsoft/AIforEarthDataSets/blob/main/data/forest-inventory-and-analysis.md) tables will often be linked by one or more primary / foreign keys.

We might be able to expose that metadata through a [ relations ] object on Column object. Each Relation would be something like

Field	Type	Description
table	string	Name of the other table. Should be present in table:columns.
columns	[ string ]	Colum names from the other table to join on

We could maybe have a type to that's one-to-one, one-to-many, many-to-one, or many-to-many. Dunno if that would be useful.

[yharby]

I accidentally opened this topic in the FOSS4G code sprint with @matthewhanson and @gadomski and maybe it can be simple as @TomAugspurger suggested but might we will need to add more optional fields to "Try" not to use the https://github.com/apache/ossie/blob/main/core-spec/spec.md#relationships ?

[yharby]

One of the use cases is File Geodatabase Domains and subtypes and relationships :

[matthewhanson]

Yeah, talked with @gadomski and @yharby and we concluded that this seems like a good approach to solve the need from portolan-sdi/portolan-cli#444

However, in the case where you wish to capture relationships between different Collections in a Catalog I think you'd want to define the table at the Catalog level, but this extension currently does not support being used in a Catalog. But even if it did I'm not sure where it would belong because these fields go in properties and there are no properties in Catalog.