Skip to main content

I am more familiar with join tables, SQL etc. I have 3 tables: “person”, “activity” and “activitylog”


“activity log” is essentially a join table that stores the person ID and activity ID.


I created two linked fields in the “activity log” table and AT created two fields for each:


“ActivityID”, “ActivityID (From activity table)”, “personID”, and “PersonID (from person table”)


I am updating the join table from Integromat and it works fine, but I don’t understand why AT creates two fields for a linked record?


What I’m trying to accomplish:



  • log all activities people do and issue rewards based on accomplishing ‘missions’

  • show me who has done what activities (and other aggregate reporting)


This is super simple in my old world of SQL server stuff, but is there a reason why AT creates 2 fields, or am I not going about this the right way?


Hey Jason - no, this is the “proper” way to create these relationships and as I recall, aside from Airtable supporting fields of type:FORMULA, this multiple field creation for relational management came as a complete surprise to me as well. I recall creating a few early designs where additional linking fields are created as the many-to-many relationships expand. It got very ugly - but it worked.


The nature of this (I think) stems from an architectural design choice concerning the inability of fields to include attributes - i.e., given any field, it would make sense to simply add the necessary methods to use its data for linking purposes. I don’t fully understand the nature of the underlying architecture but this behaviour is certainly expected by Airtable; not so much for people with relational database experience.


A similar issue arises with formula fields which requires a new field to have a formulaic computation of any kind. Ideally - as with linked fields - formulas should be possible on actual data fields instead of requiring yet another field.


The remedy is to accept this behaviour and use Views to conceal these added complexities from users - especially those who would see these fields as distractions in the data schema. With the improved security features and the ability to hide fields while forcing users into views that are less conspicuous, the result is a fully functional relational model. You just feel a little dirty about it. :winking_face:


If this answer helps, please mark it SOLVED so that the community can create detailed support metrics, thanks!


Hey Jason - no, this is the “proper” way to create these relationships and as I recall, aside from Airtable supporting fields of type:FORMULA, this multiple field creation for relational management came as a complete surprise to me as well. I recall creating a few early designs where additional linking fields are created as the many-to-many relationships expand. It got very ugly - but it worked.


The nature of this (I think) stems from an architectural design choice concerning the inability of fields to include attributes - i.e., given any field, it would make sense to simply add the necessary methods to use its data for linking purposes. I don’t fully understand the nature of the underlying architecture but this behaviour is certainly expected by Airtable; not so much for people with relational database experience.


A similar issue arises with formula fields which requires a new field to have a formulaic computation of any kind. Ideally - as with linked fields - formulas should be possible on actual data fields instead of requiring yet another field.


The remedy is to accept this behaviour and use Views to conceal these added complexities from users - especially those who would see these fields as distractions in the data schema. With the improved security features and the ability to hide fields while forcing users into views that are less conspicuous, the result is a fully functional relational model. You just feel a little dirty about it. :winking_face:


If this answer helps, please mark it SOLVED so that the community can create detailed support metrics, thanks!


thanks! Makes sense, I guess. It’s not a huge deal, just confusing when you’re used to normal relational dbs!

Reply


Terms & Conditions