Help

Migrating many-to-one relation to a new table

Topic Labels: Base design
719 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Jason_Webster
4 - Data Explorer
4 - Data Explorer

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:

Students

  • Name
  • Hours purchased - rollup from a purchases table
  • Hours tutored - rollup from the classes table
  • Hours remaining - formula, subtracting the above two from each other

Classes

  • Student - relation to Students table
  • Class Paid - checkbox of whether this class has been paid

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.

4 Replies 4

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?

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?

@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.

Jason_Webster
4 - Data Explorer
4 - Data Explorer

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.