My breaking out of [Program] as a separate table was based on [what could easily be a misunderstanding of] your comment
In my model, assuming a double diploma might consist of, say, a program (diploma? degree?) in digital media and another in journalism, each component program would be represented by its own [Program] record. In comparison, a student in purely a journalism program would only have a Program 1.
I should mention here that ‘Program 1’ and ‘Program 2’ would actually be implemented as individual instances of the linked-record field {Link to Program} from the [StudentProgram] record. Conceivably, one could link directly from [Student] to [Program], eliminating [StudentProgram], but my inclination would be to keep the intermediate [StudentProgram] table on the assumption doing so might simplify the performance of some desirable queries. (“How many students are in a digital journalism double diploma program?” for instance.)
At the [StudentProgram] level, I envisioned schedule tracking as nothing more complex than simply indicating the student’s schedule should begin on the earlier of his or her programs’ start dates and run through the later of their end dates. I would see room and instructor scheduling as operating essentially in parallel with student scheduling, with the only point of contact being, perhaps, the number of students enrolled in a given [Program] — or, perhaps more correctly, [ProgramGroup] — to ensure appropriate instructor:student ratios were met and appropriately sized classrooms reserved.
One place where the law of diminishing returns likely kicks in would be cross-program scheduling. While conceivably routines could be written to watch for conflicts between courses mandated by the component programs of a given double diploma, it’s probably easier to make that the responsibility of whatever human is tasked with arranging course schedules — especially since, if your college is anything like the one I attended, an acceptable schedule must also take into account such things as distance from instructor’s home to school, instructor’s attitude towards working on Fridays or Mondays, and an inverse relationship between likelihood of hangover and earliest class scheduled.
At some point, I recall tossing together a quick demo base involving proper sequencing of courses within a program. When I get a chance I’ll poke around my workspace and see if I can’t find it. (In the 15 months since I learned what an Airtable is, I’ve created or duplicated from a shared base roughly 160 bases, so it sometimes takes a while to figure out which one I’m vaguely remembering…)
If [ProgramGroup] refers to a specific instance of a [Program] — for instance, ‘Digital Journalism, 2018 - 2021’ — then a more accurate hierarchy might be
[Student]->[StudentProgram]->[ProgramGroup]->[Program]->[ProgramType]
or, for a student pursing a double diploma
[Student]->[StudentProgram]->[ProgramGroup]->[Program]->[ProgramType]
∟>[ProgramGroup]->[Program]->[ProgramType]
Again, it’s debatable whether [Program] is necessary — but my first inclination would be to retain it. In this model, tuition and schedules would probably reside at the [ProgramGroup] level (although internal costs, as opposed to price, might best be calculated by [Program]). In this scenario, [ProgramType] might disappear, or it might be used to house data common to a collection of [Programs] — characteristics specific to all programs representing associate’s degrees versus those representing bachelor’s degrees, for instance.
A-ha! You edited that! :winking_face:
Yes, your bottom-most diagram is close to what I was trying for with the hierarchies I limned a paragraph or so earlier.
The way I envision your work-flow is something like the following:
- [Rarely; Possibly Optional] Create
[ProgramType]s. These would be, in the broadest strokes, what types of programs your college offers: Associate’s, Bachelor’s, Master’s, professional certificates, continuing ed — whatever. I would assume these would rarely change and probably reflect, as much as anything, the school’s charter or accreditation.
- [Infrequently] Create
[Program]s. These reflect the degree (certificate, etc.) programs the school offers. I would assume these would be updated, at most, annually. Prescribed courses and sequences of study might be defined here; alternatively, they might be defined at the [ProgramGroup] level. (Edit: Actually, I would probably define them here as a linked record to [RequiredCourses], with some sort of versioning mechanism similar to that used in my pricing schedules demo. That way, one wouldn’t be forced to enter all that information each time a new [ProgramGroup] was created, but changes could be made to the list of requirements for programs starting next academic year without impacting students currently pursuing a program.)
Each [Program] would have a one-to-many link to [ProgramType].
- [Annually, perhaps more frequently] Create
[ProgramGroup]s. These represent instances of a specific [Program]. The bulk of data would be retrieved from [Program] and as such not need to be re-entered; the main variables at the [ProgramGroup] level would be those specific to a given group: schedule, tuition, and the like. (Obviously, tuition and fees could easily change from [semester-equivalent] to [semester-equivalent], and schedules will likely be set on a yearly or similar academic cycle basis. However, if [ProgramGroup] is best thought of as a cohort of students progressing together through a degree program, I’d design [ProgramGroup] to have a life expectancy equal to the amount of time one is expected to take to fulfill the degree requirements. Such things as start-stop dates and tuition could then be provided as linked records, updated annually. Similarly, depending on how monolithically students move through the program, individual course schedules and classroom assignments could be linked at whatever interval makes sense.) The goal would be to update annually/bi-annually only those fields that need modification, inheriting as much as possible from [Program].
Each [ProgramGroup] would have a one-to-many link to [Program].
Those three tiers represent your infrastructure, as such, incorporating data from general to specific as you move from item 1 to item 3. Links from tier to tier would essentially be ‘set-and-forget’: Once the link was created, it would rarely, if ever, be modified.
Against that background would be your more dynamically upodated tables: Namely, [Student] and [StudentProgram]. [Student] would be updated, unsurprisingly, for each enrolled student (or, possible, each applicant). The [Student] record would contain data particular to that student: Address, academic background, test scores, emergency contacts, payments made, and the like. As, presumably, not all students would have a declared major, some way is needed to track undeclared students, either through an ‘undeclared’ [ProgramGroup] or through appropriate data fields. (Personally, I lean toward an ‘undeclared’ group record; this would simplify the correct allocation of tuition, etc., for any student without a major.)
Students with a declared program of study would be linked to a [StudentProgram]. This would be accomplished as a drill-through from the [Student] table. I expect the [StudentProgram] record to be a relatively ‘thin’ record, primarily containing links to one or more [ProgramGroups] and a few consolidating rollup fields designed to consolidate tuition charges for each component [ProgramGroup], and the like.
During data entry, I would expect the work flow to go something like this:
- Create a
[Student] record. Enter student-specific data.
- As appropriate, create a linked
[StudentProgram] record. This would be performed by selecting the plus sign in the {StudentProgram} linked-record field and selecting ‘+ Add a new record.’ This creates a new [StudentProgram] record and drills through to the expanded record.
- As appropriate, link to one or two
[ProgramGroups]. This would be performed by selecting the plus sign in the {ProgramGroup} field in the expanded [StudentProgram] record and choosing the appropriate [ProgramGroup] record. Once a [ProgramGroup] is selected, the user is returned to the expanded [StudentProgram] record.
- After all necessary links have been created, the user selects the
X mark in the upper right-hand corner of the expanded [StudentProgram] record and is returned to the [Student] record.
At this point, necessary data from the applicable [ProgramType], [Program], and [ProgramGroup] records can propagate upward to the [Student] record, thanks to lookup and rollup fields defined along the way. This should allow the user to create and populate each student’s record with an absolute minimum (none, actually) of redundant data entry.
It would be to your benefit to search Airtable Community for references to ‘aggregation formula.’ This is an un[der]documented feature of rollup fields: Rather than simply the double handful or so of aggregation functions defined in the documentation, it is possible to define complete formulas within the configuration of a rollup field. The syntax is slightly different — namely, once you define which field you wish to roll up, you refer to that field within the formula with the keyword values. The main limitation is that one can access only a single value by reference — that is, if you are rolling up {Field A} from a linked table, there is no way also to access {Field B} from the same linked table; all other variables must refer to fields in the table that contains the rollup field. However, I expect using aggregation formulas may significantly streamline your code, especially in the [StudentProgram] table. For instance, assuming, in the case of a double diploma, tuition is calculated as 100% of the tuition cost of the higher-priced program plus a lower percentage of the other program, you could rollup {StudentProgram::Tuition} as a concatenated string containing both tuition components, identify the larger of the two, and calculate the combined tuition — all within a single rollup field; otherwise, you’d need at least two rollup fields (using aggregation functions) plus a formula field to perform the same calculation.
