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.