Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 32 Next »

Overview

"Phase" might be a bad name, esp outside of "Phase 1". "Phase 1" really is a software delivery interval, I think, with all hands on deck to try to get "core planning" functionality completed. After that the "phases" morph more into functional groupings that don't necessarily need to proceed in sequence nor constitute the same amount of work. But the ordering does reflect my general sense of how features need to be prioritized.

Phase 1 - Core Planning

Phase 1 External Data Model

Phase 1 External Data Model Schema

 

Phase 1 External Data Model Dictionary

More details to be provided in the usual integration spec

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.

ColumnTypeNotes
course_codevarchar

Table-unique course identifier. E.g. might be duplicates of formatted_course but never this field. Application does not attempt to interpret this field. Treated as a PK. Must be stable b/c it will be referred to from the MAP operational model. E.g. if external_course is truncated and rebuilt, course_code values must remain the same

formatted_coursevarchar

Same as elsewhere in the external schema. E.g. "MAT101", "ENG101"

subject_abbreviationvarcharTypically the alpha prefix on formatted_course. App should never attempt to parse formatted_course
numbervarcharTypically the numeric portion of formatted_course. Varchar b/c might be padded or non-numeric. App should never attempt to parse formatted_course
titlevarcharSelf-explanatory
descriptionvarcharSelf-explanatory
max_credit_hoursint

Upper bound for hours associated with this course when added to a plan. DM: not 100% sure on this. Advisors can override?

min_credit_hoursintLower bound for hours associated with this course when added to a plan. DM: not 100% sure on this. Advisors can override?
is_devcharFlags 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'.

external_course_term - Binds a course to a term as "offered" in that term. I.e. eligible to be planned into that term. If a course is offered in multiple terms, there is row for each such course-term pair.

ColumnTypeNotes
course_codevarcharPointer to external_course.course_code
term_codevarcharPointer to external_term.code

Phase 1 External Data Model Notes

Support for core functional requirements:

  1. Find course by keyword - Wildcard-suffixed query on external_course.formatted_course and external_course.title. Case-insensitive.
  2. Validate course-plan binding by term - Query for at least one record in external_course_term where course_code and term_code match proposed plan binding.

Course bindings to Department, Division, Program, Requisites, and Attributes all specifically excluded from this phase.

Integrators will be responsible for ensuring that external_course_term is populated far enough into the future to support the application's planning scope. There is no means by which the application will automatically "extend" existing course-term bindings nor override those bindings. Any number of options exist for handling such features in future phases.

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:

  1. external_course.course_code
  2. external_course.title
  3. external_course.formatted_course
  4. external_course_term.course_code + external_course_term.term_code

Phase 1 Operational Data Model

Phase 1 Operational Data Model Schema

Phase 1 Operational Data Model Dictionary

map_plan - Plays master to map_plan_course's detail. 

ColumnTypeNotes
iduuid

Self-explanatory 

person_iduuid

Pointer to the person to whom the plan applies (as opposed to the author). Formal FK to person.id

namevarcharUser-supplied moniker for the plan. Except for the "Is Active" checkbox, everything else spec'd in the /wiki/spaces/SSP/pages/103986449 is deferred.
object_statusint

a person_id can only have one map_plan where object_status == 1. Maps to "Is Active" checkbox in the /wiki/spaces/SSP/pages/103986449.

{audit-fields}--Standard SSP edit audit fields. created_by must equal modified_by. I.e. if a plan is edited by a non-originating user, that is a "Save As..." action.

map_plan_course - Plays detail to map_plan_course's detail.

ColumnTypeNotes
iduuidSelf-explanatory
plan_iduuidPointer to the plan which owns this course-term binding. Formal FK to map_plan.id
person_iduuidPointer 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_codevarcharPointer 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_codevarcharPointer 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_coursevarcharCopy of external_course.formatted_course. Should be something like "MAT101". Needed to produce a useful plan export should external data go missing.
course_titlevarcharCopy of external_course.title. Needed to produce a useful plan export should external data go missing.
course_descriptionvarcharCopy of external_course.description. Needed to produce a useful plan export should external data go missing.
credit_hoursvarcharCopy of external_course.min_credit_hours (per email from Jason).
is_devboolCopy of external_course.is_dev. Needed to produce useful plan export should external data go missing.
order_in_termintOrdinal representing desired on-screen position relative to other map_plan_course records in the same plan and term. Ascending order.
{audit-fields}--Standard stuff

Phase 1 Operational Data Model Notes

No roll-up hours total on map_plan b/c that's presumably an optimization we don't know that we need. But we will support output of totals on-screen, in APIs, and in reports. Just not going to require the persistent sum until we know we need it.

No object_status field on map_plan_course... no requirement to preserve deleted planned courses and a plan-course binding makes no sense without a plan, so no need to track status separately. I.e. OK to hard-delete map_plan_course records.

Please note rules in map_plan data dictionary re saving a copy when a plan is modified by a non-originating user.

The field copy from the external model into map_plan_course is potentially confusing. E.g. what value for credit_hours should be included in an API response? External or operational? In general the rule should be to trust the operational value. In this case that means whatever is stored on the map_plan_course record. So if a user suspects credit_hours is out of whack, just remove the course from the plan and re-add it.

Likely indices:

  • map_plan.person_id
  • map_plan_course.plan_id

Phase 2 - Plan Annotations

Add operational data to plans. Notes, elective type, etc.

Phase 2 External Data Model

Phase 2 External Data Model Schema

No Changes.

Phase 2 External Data Model Dictionary

No Changes.

Phase 2 External Data Model Notes

No Changes.

Phase 2 Operational Data Model

 

Phase 2 Operational Data Model Schema

Phase 2 Operational Data Model Dictionary

map_plan - New columns.

ColumnTypeNotes
contact_*varcharCollected via text fields in the /wiki/spaces/SSP/pages/103986449 dialog. Note that contact_notes are supposed to be hidden except to privileged roles.
student_notesvarcharCollected via text field in the /wiki/spaces/SSP/pages/103986449 dialog.
is_financial_aidboolCollected via checkbox in the /wiki/spaces/SSP/pages/103986449 dialog.
is_importantboolCollected via checkbox in the /wiki/spaces/SSP/pages/103986449 dialog.
is_f1_visaboolCollected via checkbox in the /wiki/spaces/SSP/pages/103986449 dialog.
academic_goalsvarcharCollected via text field in the /wiki/spaces/SSP/pages/103986449 dialog.
career_linkvarcharCollected via text field in the /wiki/spaces/SSP/pages/103986449 dialog.
academic_linkvarcharCollected via text field in the /wiki/spaces/SSP/pages/103986449 dialog.

map_plan_course - New columns

ColumnTypeNotes
student_notesvarcharCollected via UI text field. See /wiki/spaces/SSP/pages/103987058
contact_notesvarcharCollected via UI text field. See /wiki/spaces/SSP/pages/103987058. Note that value is supposed to be hidden except to privileged roles.
is_importantbooleanCollected via UI checkbox. See /wiki/spaces/SSP/pages/103987058
is_transcriptbooleanCalculated at record creation
elective_iduuidPointer to elective.id. Optional. Formal FK. If set, marks the planned course as an elective of a particular flavor, where those flavors are configured as operational reference data.

map_term_note - New table. Stores notes at the plan+term scope.

ColumnTypeNotes
iduuidStandard
plan_iduuidPointer to map_plan.id. Formal FK. Required.
term_codevarcharPointer to external_term.code. Informal FK. Required
student_notesvarcharCollected via UI text field. See /wiki/spaces/SSP/pages/103986445
contact_notesvarchar

Collected via UI text field. See /wiki/spaces/SSP/pages/103986445 

is_importantbooleanCollected via UI checkbox. See /wiki/spaces/SSP/pages/103986445
{audit-fields}--Standard

elective - Standard operational reference data type. For configuring up arbitrary flavors of "electiveness"

ColumnTypeNotes
iduuidStandard
namevarchar

Standard

codevarcharWant to start adding this to all reference types. Stable unique key (across all entities of this type regardless of status) suitable for reference from external data/applications. Often mnemonic. See SSP-526
descriptionvarcharStandard
object_statusintStandard
color_iduuidPointer to color.id. Required. Formal FK. Corresponds to color selection pull-down in admin UI. (No wireframe at this writing.)
{audit-fields}--Standard

color

ColumnTypeNotes
iduuidStandard
namevarcharStandard
codevarcharSee elective
descriptionvarcharStandard
object_statusintStandard
hex_codevarcharHex color code suitable for injection into CSS via Javascript
{audit-fields}--Standard

Phase 2 Operational Data Model Notes

UI is expected to pre-populate contact_* form fields with the currently authenticated user's attributes. We intentionally take copies of those values into map_plan at Russ's direction. Advisors are sometimes reluctant to expose certain attributes to students. We choose to accept the potential for out of sync attributes as a result.

Uncertain how frequently map_plan_course.is_transcript should be recalculated. Since this needs to be stored on the plan as data that will survive external data loss, we will assume it is handled just like all other such fields and is only calculated/copied at map_plan_course record creation time. If you want it recalculated, you need to remove/readd the course. Expect this behavior to change since transcription state should be expected to change over time. DM: Or might just not persist this at all. Awaiting word from Russ.

object_status intentionally left off of map_term_note. No soft-delete requirements there.

Restricted access to the various contact_notes fields must be handled server-side. I.e. the API can't just always return contact_notes whenever looking up a plan and hope the UI enforces permissions correctly. Same for writes, but in the other direction.

Phase 3 - Enhanced Search

Phase 3 External Data Model

Phase 3 External Data Model Schema

Phase 3 External Data Model Dictionary

TBD

Phase 3 External Data Model Notes

We don't need a list of programs without courses to support search features, but we require external_program anyway b/c we know we need that feature for plan templates (Phase 4). The denormalization of program names into external_course_program is an optimization to help search, though, which really only does need the list of programs having at least one required course. This lets that lookup avoid one join. And denormalization in external data is OK as a general rule.

More TBD

Phase 3 Operational Data Model

Phase 3 Operational Data Model Schema

Phase 3 Operational Data Model Dictionary

TBD

Phase 3 Operational Data Model Notes

TBD

Phase 4 - Templates

Phase 4 External Data Model

Phase 4 External Data Model Schema

Phase 4 External Data Model Dictionary

TBD

Phase 4 External Data Model Notes

TBD

Phase 4 Operational Data Model

Phase 4 Operational Data Model Schema

Phase 4 Operational Data Model Dictionary

TBD

Phase 4 Operational Data Model Notes

TBD

Phase 5 - Course Details

Phase 5 External Data Model

Phase 5 External Data Model Schema

Phase 5 External Data Model Dictionary

TBD

Phase 5 External Data Model Notes

TBD

Phase 5 Operational Data Model

Phase 5 Operational Data Model Schema

No changes.

Phase 5 Operational Data Model Dictionary

No changes.

Phase 5 Operational Data Model Notes

We do not copy any of the new external_course fields onto the map_plan_course operational model, so no changes.

Phase 6 - ???

  • No labels