Consider allowing one to many relationships?

[nyalldawson]

Would you consider adding official support for one to many relationship types to the related tables extension? The requirement that all relationships must be many to many considerably complicates the specification for simple use cases, and makes working with relationships in geopackages in clients much trickier then it often needs to be.

[jyutzler]

Do you really need an extension for a simple join based on a primary key / foreign key pair? The Related Tables Extension is useful because it defines the many-to-many relationship. While there is nothing keeping you from using it for a one-to-many relationship, it is not clear to me why one would be compelled to do so. Just define an attributes table (see https://opengeospatial.github.io/e-learning/geopackage/text/attributes_extensions.html#id1 or https://www.geopackage.org/spec140/#attributes) and make a foreign key.

[nyalldawson]

@jyutzler

Just define an attributes table (see https://opengeospatial.github.io/e-learning/geopackage/text/attributes_extensions.html#id1 or https://www.geopackage.org/spec140/#attributes) and make a foreign key.

That's the approach I was thinking of going for in gdal (it's what gdal uses for defining relationships in sqlite data sources too). But it feels messy to me, because then for gpkg we have two competing ways to define relationships. An application would then need to check both for foreign key constraints AND for the related tables extension in order to fully understand the relationships in a gpkg (and then take care not to incorrectly think that constraints involved in tables which follow the extension aren't relationships in themselves). It's a lot of complex and fragile logic which could be avoided if non -many to many relationships were instead officially supported and described in the specifications.