Overview

SSP "external" tables/views do not enforce referential integrity, but loose, logical dependencies do exist between them. In some cases, even when such a logical dependency exists, it may not necessarily be the case that the depended-on record actually exist in order for the system to function. So producing a completely accurate dependency graph would be a complex effort and likely frequently subject to change as the application evolves. But some deployers have requested guidance on a recommended order in which to update these tables to ensure the complete dataset is an internally consistent as possible at any given time. This document provides such a recommendation. Keep in mind of course that this is just that - a recommendation. Not all the tables listed are required and other orderings might be perfectly valid.

For inserts the ordering is from top-to-bottom, i.e. insert into tables in the order listed. Updates would typically be in the same order as inserts, but ordering is generally less of an issue. Ordering is reversed for deletes (which should be rare). I.e. delete from bottom to top.

This document also provides a "primary use" column which tries to indicate the SSP subsystem that makes heaviest use of the data in any given table. This is intended to help provide implementation prioritization guidance when trying to decide which tables to "support first", inter-table dependency considerations aside. E.g., while external_faculty_course may be listed ahead of external_course, these tables have no logical dependency relationship. So if your deployment is more interested in the MAP subsystem than in the Early Alert subsystem, you needn't populate external_faculty_course before populating external_course.

List of External Tables

Table	Logical Dependencies	Primary Tool Use
external_course	`external_department`, `external_division` (You might think this table should precede `external_student_transcript_course` in a sort of master-detail relationship. But `external_student_transcript_course` uses a less strict definition of course and the application does not use `external_course` to provide additional context when rendering `external_student_transcript_course` records. There is a relationship between these two tables, but if anything it flows the other direction, i.e. at the app layer `external_course` records are checked against `external_student_transcript_course` to determine if a planned course has already been taken.)	MAP-Available Courses
external_course_program	`external_course`, `external_program`	MAP-Program Filter
external_course_requisite	`external_course`	MAP-Available Courses, planned course validation
external_course_tag	`external_course`	MAP-TAG Filter, Available Courses
external_course_term	`external_term`, `external_person`	MAP-Available Courses, planned course validation
external_department		MAP-Template definition
external_division		MAP-Template definition
external_faculty_course	`external_term`, `external_person`	Early Alert Portlet-Early Alert course list
external_faculty_course_roster	`external_term`, `external_person`, `external_faculty_course_roster`	Early Alert Portlet-Early Alert course list roster
external_person	`external_term`, `external_department`, `external_person`	Caseload Assignment-Add and Edit students Main-Dashboard and Details tabs Reports-Counselor Case Management Report
external_person_note	`external_person`	Notes-Student Notes tool
external_person_planning_status	`external_person`	Main-Dashboard tab, MAP summary MAP-Plan Status indicator Reports-Number of Students by Plan Status
external_program		Caseload Assignment-Student Search MAP-Program Filter, Template definition
external_registration_status_by_term	`external_term`, `external_person`	Main-Details tab Recent Term Activity Reports-Counselor Case Management Report
external_student_academic_program	`external_program`, `external_person`	Main-Dashboard and Details tabs, MAP summary
external_student_financial_aid	`external_person`	Main-Dashboard and Details tabs MAP-Financial Aid pop-up Reports-Counselor Case Management Report
external_student_test	`external_person`	Main-Placement tab
external_student_transcript	`external_person`	Main-Dashboard and Details tabs Reports-Counselor Case Management Report
external_student_transcript_course	`external_person` (Note that while this table refers to the same notion of a "course" as `external_faculty_course` and should use the same course and section identifiers, there isn't really even a logical dependency between the two tables because `external_faculty_course` should only contain "early alertable" courses whereas a student transcript should list all transcripted courses for any given student.)	Main-Transcript and Schedule tabs MAP-Transcripted planned course validation
external_student_transcript_term	`external_term`, `external_person`	Main-Details tab Reports-Counselor Case Management Report
external_term		Caseload Assignment-Anticipated Start Year and Term Student Intake-Anticipated Graduation Year and Term MAP-Plan and Template year and term grid

Student Success Plan

SSP External Database Logical Dependency Ordering

Overview

List of External Tables

MAP Related External Tables

Person Related External Tables

Early Alert Related External Tables