Airtable to Convex: bidirectional links?
Hey! Curious yout thoughts about how best to design bidirectional links like in Airtable. Just curious about your thought regarding best architecture, in terms of consistency, flexibility, indexing, and atomicity.
One thing to consider, in airtable when creating linked field, it will always be matched with a new "mirrored" field on the table you linked to.
6 Replies
I'm the lone dev at at a small company that uses Airtable pretty aggressively for all of our data storage (we're on Airtable's Enterprise plan because of our data storage requirements). I'm early in the process of migrating all of that to Convex, and I had a similar question shortly after discovering Convex and trying to understand the Convex way of things. In the end, I'm not trying to do bidirectional links at all.
What I'm doing instead is taking a careful look at the relationships, deciding which side of the link is the most important, and only building the relationship in one direction. With Convex, it's easy enough to find both sides as long as you have well-crafted indexes.
Here's a simple example. In our business, two of our tables are
deals and quotes. A deal can have multiple quotes, but a quote can only have one deal. In Airtable the links obviously go both ways. In Convex, I've chosen to not put any quote-related data on the deal because the deal is "higher" in the hierarchy. I only added a dealId field to the quotes table, with an index on that field. Finding the deal from the quote is easy because of that ID. To find all quotes tied to a deal, I'll query the quotes table using that index, searching for the ID of a specific deal.
For a many-to-many relationship in Convex, I'll often build a junction table. For example, a deal can have many contacts, and each contact can be tied to many deals. In that case I made a dealContacts table that only stores two IDs: one for the deal, one for the contact. Both fields are indexed, so finding all deals for a given contact or all contacts for a given deal is extremely efficient.Hi @Clever Tagline , I appreciate the response.
This is an interesting approach. I'm curious why junction table vs other methods which are possible in Convex?
Which other methods are you referring to?
Examples: Event trigger and sync both sides in real time,
utalizing relationship helpers
I haven't looked into the relationship helpers that deeply yet. Unfortunately I haven't been able to devote much time to the migration process lately because there are more pressing needs that keep me in the current Airtable setup.
As for the event triggers, I know of their existence, but again haven't looked into them very deeply for the same reason.
I wonder what the Convex team would suggest as the best desgin decision for this use case.@Wayne?
( @Convex Bot @Convex Indexer @convex-threads @Carl-bot @Convex Verification )