Related-Tables-Extension - direction represented by a user-defined mapping table

[jechterhoff]

Chapter 6.1 "Overview" of the Related Tables Extension (RTE) states:

The relationships can be considered directional, in that they relate primary keys of two tables in terms of base (the "left" or "from" side of the mapping) and related (the "right" or "to" side of the mapping).

Question: Would a bi-directional association between two feature types (defined in an application schema) then have to be represented as two user-defined mapping tables?

If so, then as far as I can see, the content of these two tables would be the same - only that the values for columns base_id and related_id would be switched.

A user-defined mapping table can represent an n:m relationship between two tables (which can be restricted to n:1, 1:n, and 1:1 by the application itself). Such a relationship can be read both ways, so a second mapping table would not actually be needed to represent a bi-directional association.

I am interested to find out what is expected by GeoPackage standard-compliant applications, when they intend to encode a bi-directional association:

1. Does the RTE mandate that two user-defined mapping tables be created - and maintained - by the application?
2. Or should there be only one mapping table to represent the bi-directional association (then ignoring the directionality implied by base_id and related_id)?
3. Or is this unspecified in the RTE standard, and thus up to the application? ... at least until clarified by a future revision of the RTE standard ...

Any feedback on this topic would be highly appreciated. Thanks.

[jyutzler]

Yes, you would be able to read the relationships in either direction. For example, in the example, if you wanted to find out which features were described by Media 18, you could query the related_id from the join table.

The Standards Working Group is considering a revision to the Related Tables Extension. If you have any recommendations on how we could make this more clear, we would love to hear them.

[jechterhoff]

On the one hand, the current text regarding directionality should be revised, to clarify that, in principle, the relationship can be read both ways.

On the other hand, we could introduce another optional column in the gpkgext_relations table, called "direction". The allowed values for this column would be bi-directional, base-to-related, and related-to-base - with bi-directional being the default if no value is provided (which would also be in line with current behavior). Applications could use the information to show related data to the user as appropriate for the chosen column value.

Furthermore, another useful metadata item in the gpkgext_relations table could be whether the relationship between base and related table is n:m, n:1, 1:n, or 1:1. This information can help applications in enforcing the multiplicity constraints intended for the data (e.g. defined by an application schema). Certainly, there are other ways in relational databases to represent the non-n:m cases - but they would change the related tables extension quite drastically, afaics.