Table of Contents

Overview

Previous versions of this doc called its top-level organizational categories "Phases". That was a bad name outside of the "Core Planning" category, b/c while that matched up with the first software delivery interval, everything else was more of an abstract set of feature groupings that happened to be listed in perceived priority order, but could technically be reordered with minimal design impact. So I've switched out "Phase" for "Feature Set".

Feature Set 1 - Core Planning

Feature Set 1 External Data Model

Feature Set 1 External Data Model Schema

Feature Set 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 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.

...

Feature Set 1 External Data Model Notes

Support for core functional requirements:

...

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

Feature Set 1 Operational Data Model

Feature Set 1 Operational Data Model Schema

Feature Set 1 Operational Data Model Dictionary

map_plan - Plays master to map_plan_course's detail. 

...

Feature Set 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.

...

• map_plan.person_id
• map_plan_course.plan_id

Feature Set 2 - Plan Annotations

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

Feature Set 2 External Data Model

Feature Set 2 External Data Model Schema

No Changes.

Feature Set 2 External Data Model Dictionary

No Changes.

Feature Set 2 External Data Model Notes

No Changes.

Feature Set 2 Operational Data Model

Feature Set 2 Operational Data Model Schema

Feature Set 2 Operational Data Model Dictionary

map_plan - New columns.

...

...

Feature Set 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 Prior to May 16, 2013, this design included a 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  field. Persisting that value no longer makes sense given the priorities outlined below in Feature Set 7. It will now be a calculated value from the application's perspective. The issue was discussed on the ssp-dev list.

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.

Feature Set 3 - Enhanced Search

Feature Set 3 External Data Model

Feature Set 3 External Data Model Schema

Feature Set 3 External Data Model Dictionary

TBD

Feature Set 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 (Feature Set 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

Feature Set 3 Operational Data Model

Feature Set 3 Operational Data Model Schema

Feature Set 3 Operational Data Model Dictionary

TBD

Feature Set 3 Operational Data Model Notes

TBD

Feature Set 4 - Templates

Feature Set 4 External Data Model

Feature Set 4 External Data Model Schema

Feature Set 4 External Data Model Dictionary

TBD

Feature Set 4 External Data Model Notes

TBD

Feature Set 4 Operational Data Model

Feature Set 4 Operational Data Model Schema

Feature Set 4 Operational Data Model Dictionary

TBD

Feature Set 4 Operational Data Model Notes

TBD

Feature Set 5 - Course Details

Feature Set 5 External Data Model

Feature Set 5 External Data Model Schema

Feature Set 5 External Data Model Dictionary

TBD

Feature Set 5 External Data Model Notes

TBD

Feature Set 5 External Data Model Dictionary

TBD

Feature Set 5 External Data Model Notes

Values for requisite_code were discussed on the dev list.

Trying to use only two requisite_code values to model a bi-directional "Co" restriction will not work. E.g. BIO+BIO_LAB must be taken as a pair. If you model BIO->BIO_LAB (Pre-Co) and BIO_LAB->BIO (Pre-Co), then you'll catch cases where they're taken independently and *only* independently. But if you have a plan where they're taken as a pair in one term, but then BIO is added again in a later term, the mistake won't be caught.

Feature Set 5 Operational Data Model

Feature Set 5 Operational Data Model Schema

No changes.

Feature Set 5 Operational Data Model Dictionary

No changes.

Feature Set 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.

Feature Set 6 - On/Off Plan

Feature Set 6 External Data Model

Feature Set 6 External Data Model Schema

Feature Set 6 External Data Model Dictionary

TBD

Feature Set 6 External Data Model Notes

TBD

Feature Set 6 Operational Data Model

Feature Set 6 Operational Data Model Schema

No Changes

Feature Set 6 Operational Data Model Dictionary

No Changes

Feature Set 6 Operational Data Model Notes

No Changes

Feature Set 7 - Transcripted Course

Feature Set 7 External Data Model

Feature Set 7 External Data Model Schema

Feature Set 7 External Data Model Dictionary

Feature Set 7 External Data Model Notes

No new external tables.

The basic requirement is to indicate to the end user whenever viewing a plan or adding/editing a course to/in a plan when that course already appears in the user's transcript. Per Jason, "The business requirement is to check the planned courses to see if the student took them....Speed and accuracy are the keys." Because of that (esp the "accuracy" part), we're not going to copy a "transcripted" flag/status onto the operational model. Determining whether or not a planned course is already transcripted will always be a transient join/lookup.

...

select 1 from v_external_student_transcript_course
where school_id = :plan_student_id 
and formatted_course = :planned_formatted_course
and term_code = :planned_term_code

Forward-looking: 

select 1 from v_external_student_transcript_course
where school_id = :plan_student_id 
and formatted_course = :planned_formatted_course
and term_code in (select term_code from external_course_term where course_code = :planned_formatted_course)

Our transcript table/s do not know about course code. We accept the potential ambiguity despite the stated priority of "accuracy". That value is likely just not available in transcript feeds. 

Feature Set 7 Operational Data Model

Feature Set 7 Operational Data Model Schema

Feature Set 7 Operational Data Model Dictionary

Feature Set 7 Operational Data Model Notes