Camille,
I’m very new to Tadabase, so it’s possible I’m way off base here. I trust Moe or someone will correct me if I am.
But I’m not new to databases, in fact, I’m an experienced database applications developer and have built enrollment systems for universities. The problem you’re talking about is a classic problem with a classic solution. Notwithstanding Tadabase’s somewhat non-classic way of connecting records, I’m wondering if the classic approach might not help you here.
Background
In a more conventional database, the textbook way to implement many to many relationships between, say, tables A and Z is to use a “join” table M that sits in the middle between each of the “many” entities. School enrollment databases are in fact the classic example of this type of relationship.
Here’s how it works (in the classical systems). You have tables called Students and Teachers, of course. But you don’t relate them to each other directly. Instead you create a third table called Enrollments, with these fields:
Enrollment Number (a unique but otherwise not very meaningful value)
Teacher ID
Student ID
And you connect Enrollments to Teachers on one side and Students on the other. Each of those relationships is a one-to-one relationship. So every record in the Enrollments table represents one student assigned to one teacher. If a student is enrolled in classes with more than one teacher, then he or she will be represented in the Enrollments table by more than one record.
Pros and Cons
The advantage of the join table is maximum flexibility. Only good example I can think of right now is, say the school wants to know how many girls are enrolled in all classes, or only in Ms Ortega’s classes. Assuming Students table includes a Sex field, this should be easy to find by searching in Enrollments.
A possible con (especially in the context of low-code systems like Tadabase or Airtable) is that, if you want, from the perspective of the Teacher record, so see a list of students, the platform (say, Tadabase) has to be able to reach through the Enrollments table to the Students table two hops away. But even if you use a platform that supports that, you’ll probably end up using the Enrollments table most of the time in any case, because of its flexibility.
Doing it in Tadabase: the tables and connections
I just threw this together in Tadabase. Let me say again: I’m a Tadabase novice so proceed with caution, if not downright skepticism. And I admit I see a couple little problems that I myself don’t offhand know how to solve. But here’s what I did.
I created very simple tables for Teachers and Students, with just a Name in each table.
Then I created an Enrollments table. It needs to have a display field so I called it ENum (for “Enrollment Number”). I imagine this would be a unique number but I was just entering values by hand as I created enrollment records; it’s actually not very important here. After creating that one field, I then created two connections:
- Enrollments-to-Teachers
- Enrollments-to-Students
Here is what the Enrollments table looks like in the Data Builder:
Here’s what the connection diagram looks like:
Doing it in Tadabase: The pages
Finally, I created a page called Enrollments. I placed on it two basic components: a table showing enrollment records by student and another table showing enrollment records by teacher. [NOTE: This is an edit to what I originally posted, which I think might have been confusing.] I’ll skip over the details, but I configured each of these components so that the items were grouped (by student name in the Students component and by teacher name in the Teachers component) and I enabled the show/hide toggle. This is what I see now:
Note that student Jenny is enrolled in classes both with Mr Johnson and Ms Ortega. Teacher Mr Johnson currently has two students, Jenny and Luis.
Couple of comments
I made a single page just to make showing it to you easy. In real life, I’d create separate pages for students and teachers, add links to the menu. But the key thing to note is that each of these pages would be based on the Enrollments table. The “raw” Students and Teachers tables would be used only for basic data entry of person’s names and a handful of other qualities (sex and DOB for students, perhaps, SSN perhaps for teachers).
I’d also add filters or whatever I needed to be able to search for particular teachers or students.
The practical use case for many-to-many relationships (as distinct from the easier example we’ve been discussing) involves not just teachers and students but CLASSES, and in the university settings, there’s often a distinct entity called COURSES. So both Mr Johnson and Ms Ortega might be linked to the course French 101, but each teaching their classes at different times. It can get complicated – but the basic approach is just an extension of what I said above.
If this just muddied the waters I hope you stopped reading five minutes ago. 
William
