Back working on Coda after 9 months spent on other priorities (teaching during a pandemic!) and I found I have either forgotten a lot or never learned it as well as I thought…
I am setting up my data structures for a new project (teacher/student flexible tutorial scheduling, with a Coda front end for user interface and to store and preprocess data, IBM CPLEX back end to optimize schedules based on data) and I keep running into a set of related design issues.
My data has many one-to-many elements (students take multiple classes, teachers teach multiple classes). The natural Coda structure there is a key (student) and multi-select (from a courses table). Easy interface, select course from dropdown using lookups.
First problem: courses have multiple people teaching them. I can add a multi-select teacher column to the student table, pulling from the teacher table, nicely filtered to only show the relevant teachers for those courses. BUT now for each student I have a list of courses and a list of teachers, and they are just two lists - not ordered pairs, as I want them. I could try and rely on order in the two lists to keep data straight, but that… makes me nervous. (I really need “tuples” from CPLEX, which I love!) Is there a way to fake ordered pairs?
Second related problem: there are places where the multiple-entry works fine, but I want to have a user interface where, for an individual student I take a column with multiple entries (their courses) and create a new table where each entry in the cell is a row (so that preference data can be entered). In other words, multi-select cell → multiple rows disaggregated.
I know I can solve both problems by having a flat base table that is classic single-entry (so multiple rows for each student, one for each class, and so on) and adding a whole bunch of list-generating formulas to make it look like it is multi-select. But I hate that! So I am hoping there is a better, truly relational Coda solution.
Hey there @Andrew_Milne !
I’m a public school teacher (High School) and have been making and using documents in the school context for the past three years. Would love to help out - but feel like I could provide more targeted feedback and examples if you provided an example doc or more context about the specific tool you are building (you mentioned that it is a flexible tutorial scheduling tool - could you provide more context as to exactly what it’s going to accomplish?)
Thanks!
2 Likes
Thanks for the offer! The plan is to use Coda as a more flexible database for information that is in our Student Information System (Infinite Campus in our case), and as a more friendly app-like user interface for data collection and delivery of results. We hope to offer one (maybe two) 90 minute flexible tutorial blocks per week, scheduled according to teacher needs and student preferences.
Here is the quick summary:
Data (teachers, students, classes) goes into Coda tables.
Teams of teachers responsible for particular courses decide what kinds of tutorial options to offer for those classes (individual tutorials, group tutorials, test reviews, lab make-ups, etc). Teachers can set associated parameters (min/max numbers, min/max lengths, number of possible repetitions). Teachers need an interface that lists their classes, their available tutorial options, and the ability to set priorities and parameters.
Students get a “menu” of available options given their courses and teachers, and place priorities on those options.
Data on options for courses and teachers, data on student preferences over their potential options is exported to IBM CPLEX, which generates schedules based on optimization techniques saying which students go where when for which activities, which go back to Coda for display to teachers and students.
That is the big picture plan!
The original question is about the difference between the following:
The student schedule:
where as you can see, each student has multiple lines with a single course, so each course can be unambiguously associated with a single teacher, and…
The teacher schedule:
where as you can see a single teacher is associated with multiple courses in a single line.
What I’d like to be able to do is avoid the redundancy of the student method (but that would require some way of getting pairs (course-teacher) data elements that is user-friendly). And on the other hand, with the teacher method, I need to be able to create a table for teacher 1 with course 1 and course 2 on different rows.
Hey @Andrew_Milne ,
I would honestly structure it the first way, where each class is its own unique row (others might argue differently though). I know it feels redundant and longer to set up in the first place but should provide more flexibility in the long run. The only reason I would possibly stray away from this is if it would lead to a massive amount of rows (ballpark greater than 10,000) and potentially slow your document down.
As far as getting the teachers courses into their own row, I would use a formulaMap() formula in a button to add a row to a new table. Here is an example:
Hopefully that gives you some direction! If you provide an example doc with more context, Would love to assist further.
3 Likes
Hey, I just found I had typed a “thank you” but not hit submit! So late thanks, but very big thanks - I am using this schema and it is working great!
