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.

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.

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

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 feature sets.

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

map_plan_course - Plays detail 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.

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

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.

map_plan_course - New columns

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

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

color

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.

Prior to May 16, 2013, this design included a map_plan_course.is_transcript 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

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.

There are two types of checks for an already transcripted course:

 Backward-looking:

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