This is certainly an ugly issue because APIs should be naturally efficient about retrieving data given any single record ID. Lacking an API that supports helper methods to interrogate the schema and relationships, we’re basically on our own.
In a 25,000 record table, that’s 24,999 times the amount of data space actually required. Sure, it would work, but oh my - what a waste of resources.
Before doing that, consider using the table ID concatenated with the record ID (delimited with a dot or something). That would be at least a little more space-efficient.
Possible Better Hack?
What if you added a new field (such as “Linked Table”) and on one record – and one record only - you populate this field with the secondary table name?
- As we know, the API never returns empty values, ergo - this Linked Table field will never be visible in the JSON results (except for one record).
- We also know that a query to find the Linked Table requires only that we filter based on
Linked Table != "" (i.e., not empty).
- In a dynamic environment, a single query into the primary table returning a single result record would provide the secondary table name without forcing any bloat in the primary table, and without redundant values nor any redundant filters that need to process.
This proposed approach is simply a less pukey hack that requires an interrogation of the primary table. However, it would be relatively performant and would meet the need for a single API process to perform dynamically driven linked table identification.