Sep 05, 2022 07:41 AM
I’m working on migrating data between tables. For context, let’s say I have designed these 2 tables for a tutoring company that offers bulk tutoring with different packages:
The Classes table has a record of booked classes, but not all are paid. We use an automation script that monitors the Students table for any changes to the hours purchased for a student (say through Stripe, or a manual change by sales), and updates the field in the Class Paid column for any of that student’s classes accordingly.
This worked for a one-to-one relationship between a student and a class. However, we’ve begun offering classes to multiple students at a time. The “Class Paid” checkbox is now invalid, as two students could have paid for a different number of classes. This checkbox should instead be tied to the Student<>Class relationship.
I’d like to decouple the two tables and create a purely relational table (similar to how one might do this in SQL).
It’s a fairly straightforward piece of code to migrate these tables and remove the coupling. My question is is there an easy way to do this on airtable? TIA.
Sep 05, 2022 07:50 AM
Welcome to the Airtable community!
It sounds like you are moving from a one-to-one relationship to a many-to-many relationship. Each student could have multiple classes, and each class can have many students. In order to keep track of which students have paid for which classes, you would need a junction table to store who has paid for what.
Do you want help understanding what a junction table is? Or do you want help in how to migrate to a junction table setup in Airtable? Or something else?
Sep 06, 2022 02:52 AM
That’s perfect! Exactly the type of table I needed.
I would like some help in migrating existing data to a junction table. Assuming the junction assistant can help here?
Sep 06, 2022 05:26 AM
@Kamille_Parks can tell you more about Junction Assistant.
However, upon re-reading your post, I think that your use case is a bit more complex because you also have the separate purchases table. Since purchases are not tied to specific classes, you might need additional logic to decide how hours purchases are applied when there are multiple outstanding classes or partial payments.
My gut instinct is that there isn’t enough info about your base schema and your business model to answer you question.
Sep 16, 2022 12:46 AM
I don’t think that’s needed.
Ideally just need to know how to migrate an existing Students <-> Classes table to a Students → Junction ← Classes setup.