| Table of Contents |
|---|
Overview
"Phase" might be Previous versions of this doc called its top-level organizational categories "Phases". That was a bad name , esp outside of the "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
...
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
| Gliffy | ||
|---|---|---|
|
...
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 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 |
...
Feature Set 1 External Data Model Notes
Support for core functional requirements:
...
Course bindings to Department, Division, Program, Requisites, and Attributes all specifically excluded from this phasefeature 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 phasesfeature 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:
external_course.course_codeexternal_course.titleexternal_course.formatted_courseexternal_course_term.course_code+external_course_term.term_code
...
Feature Set 1 Operational Data Model
...
Feature Set 1 Operational Data Model Schema
| Gliffy | ||
|---|---|---|
|
...
Feature Set 1 Operational Data Model Dictionary
map_plan - Plays master to map_plan_course's detail.
| Column | Type | Notes | ||||
|---|---|---|---|---|---|---|
| id | uuid | Self-explanatory | ||||
| person_id | uuid | Pointer to the person to whom the plan applies (as opposed to the author). Formal FK to | ||||
| nameowner_id | varchar | User-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_status | int | a person_id can only have one map_plan where object_status == 1. Maps to "Is Active" checkbox in the uuid | Pointer to the person who is ultimately responsible for all edits to this plan. Formal FK to person.id. Immutable. A change in ownership should result in a copy of the plan, i.e. the equivalent of a "Save As..." operation. We are avoiding use of the audit fields for tracking this for at least two reasons... we want to refactor that implementation at the Java level (SSP-479) and API usage by automated systems potentially muddies the semantics of audit fields. |
| name | varchar | User-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_status | int | a | ||||
| {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.Do not use these for business rules related to ownership/authorship changes. |
map_plan_course - Plays detail to map_plan_course's detail.
| 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 |
...
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_idmap_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
| Gliffy | ||
|---|---|---|
|
...
Feature Set 2 Operational Data Model Dictionary
map_plan - New columns.
| Column | Type | Notes |
|---|---|---|
| contact_* | varchar | Collected 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_notes | varchar | Collected via text field in the /wiki/spaces/SSP/pages/103986449 dialog. |
| is_financial_aid | bool | Collected via checkbox in the /wiki/spaces/SSP/pages/103986449 dialog. |
| is_important | bool | Collected via checkbox in the /wiki/spaces/SSP/pages/103986449 dialog. |
| is_f1_visa | bool | Collected via checkbox in the /wiki/spaces/SSP/pages/103986449 dialog. |
| academic_goals | varchar | Collected via text field in the /wiki/spaces/SSP/pages/103986449 dialog. |
| career_link | varchar | Collected via text field in the /wiki/spaces/SSP/pages/103986449 dialog. |
| academic_link | varchar | Collected via text field in the /wiki/spaces/SSP/pages/103986449 dialog. |
...
| Column | Type | Notes | |||
|---|---|---|---|---|---|
| student_notes | varchar | Collected via UI text field. See /wiki/spaces/SSP/pages/103987058 | |||
| contact_notes | varchar | Collected via UI text field. See /wiki/spaces/SSP/pages/103987058. Note that value is supposed to be hidden except to privileged roles. | |||
| is_important | boolean | Collected via UI checkbox. See /wiki/spaces/SSP/pages/103987058 | is_transcript | boolean | Calculated at record creation |
| elective_id | uuid | Pointer 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. |
...
| Column | Type | Notes |
|---|---|---|
| id | uuid | Standard |
| name | varchar | Standard |
| code | varchar | See elective |
| description | varchar | Standard |
| object_status | int | Standard |
| hex_code | varchar | Hex color code suitable for injection into CSS via Javascript |
| {audit-fields} | -- | Standard |
...
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 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
| Gliffy | ||
|---|---|---|
|
...
Feature Set 3 External Data Model Dictionary
Phase 3 External Data Model Notes
Phase 3 Operational Data Model
Phase 3 Operational Data Model Schema
| Gliffy | ||
|---|---|---|
|
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
| Gliffy | ||
|---|---|---|
|
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
| Gliffy | ||
|---|---|---|
|
Phase 4 Operational Data Model Dictionary
TBD
Phase 4 Operational Data Model Notes
TBD
Phase 5 - Course Details
...
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
| Gliffy | ||
|---|---|---|
|
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
| Gliffy | ||
|---|---|---|
|
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
| Gliffy | ||
|---|---|---|
|
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
| Gliffy | ||
|---|---|---|
|
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.
| Code | Definition |
|---|---|
| PRE | requiring_course_code requires that required_course_code be taken in a previous term |
| PRE_CO | requiring_course_code requires that required_course_code be taken in a previous term or concurrently |
| CO | requiring_course_code requires that required_course_code be taken in concurrently |
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
| Gliffy | ||
|---|---|---|
|
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
| Gliffy | ||
|---|---|---|
|
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:
| Code Block |
|---|
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:
| Code Block |
|---|
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