...
external_course - A course "definition" as distinct from any one or more term-specific offerings of that course. If an institution "redefines" a course such that "ACCT101" has new sematics, that's a new row where formatted_course remains "ACCT101", but course_code is a new value. Integrators are well-advised to put and keep all course definitions in this table which might or have already been added to student plans.
| Column | Type | Notes |
|---|---|---|
| course_code | varchar | Table-unique course identifier. E.g. might be duplicates of |
| formatted_course | varchar | Same as elsewhere in the external schema. E.g. "MAT101", "ENG101" |
| subject_abbreviation | varchar | Typically the alpha prefix on formatted_course. App should never attempt to parse formatted_course |
| number | varchar | Typically the numeric portion of formatted_course. Varchar b/c might be padded or non-numeric. App should never attempt to parse formatted_course |
| title | varchar | Self-explanatory |
| description | varchar | Self-explanatory |
| max_credit_hours | int | Upper bound for hours associated with this course when added to a plan. DM: not 100% sure on this. Advisors can override? |
| min_credit_hours | int | Lower bound for hours associated with this course when added to a plan. DM: not 100% sure on this. Advisors can override? |
| is_dev | char | Flags course as "developmental." Common enough at community colleges to model as a first-class attribute rather than as metadata. Also, the app applies special handling to this when displaying plan hour totals. So our appoach to handling metadata, which is completely agnostic to any semantics on any metadata tag/s, wouldn't work anyway. It's a char b/c we've historically represented booleans in external data as 'Y'/'N'. |
...
| Column | Type | Notes |
|---|---|---|
| course_code | varchar | Pointer to external_course.course_code |
| term_code | varchar | Pointer to external_term.code |
...
Traditionally, external data model schema implementations have not included formal foreign keys. Physical tables should be indexed by logical foreign keys and any other commonly queried fields:
external_course.course_codeexternal_course.titleexternal_course.formatted_courseexternal_course_term.course_code+external_course_term.term_code
...
| Column | Type | Notes |
|---|---|---|
| id | uuid | Self-explanatory |
| plan_id | uuid | Pointer to the plan which owns this course-term binding. Formal FK to map_plan.id |
| person_id | uuid | Pointer to the person to whom the plan applies (as opposed to the author). Formal FK to person.id. Must always match map_plan.person_id for a given plan_id. This is a denormalization for performance. |
| term_code | varchar | Pointer to the term in which the course is planned. Informal FK to external_term.code. Integrators are strongly encouraged to use term_codes with business meaning, e.g. FA12 not 1234. This seems common enough that taking a copy of the term name/title is not necessary. |
| course_code | varchar | Pointer to the course being planned. Informal FK to external_course.course_code. Recall that this is a potentially opaque value, not something like "MAT101". |
| formatted_course | varchar | Copy of external_course.formatted_course. Should be something like "MAT101". Needed to produce a useful plan export should external data go missing. |
| course_title | varchar | Copy of external_course.title. Needed to produce a useful plan export should external data go missing. |
| course_description | varchar | Copy of external_course.description. Needed to produce a useful plan export should external data go missing. |
| credit_hours | varchar | Copy of external_course.min_credit_hours (per email from Jason). |
| is_dev | bool | Copy of external_course.is_dev. Needed to produce useful plan export should external data go missing. |
| order_in_term | int | Ordinal representing desired on-screen position relative to other map_plan_course records in the same plan and term. Ascending order. |
| {audit-fields} | -- | Standard stuff |
...