Background for the Data Model Migration

Person Centric Data Model

One of the significant enhancements that will be gained with Anthology products is the ability for applications to share and reuse person centric data. Even within individual applications such as CampusVue Student, there is a large amount of redundancy in person centric data that is stored. For example, if a person attends school as a student and later becomes a staff member at the same institution, the current implementation in CampusVue Student requires completely separate and disparate records be stored for the student and the staff member even though it is the same person. Name, address, phone number, and email address information is some of the data that is keyed in twice in this scenario. This obviously results in extra data entry for the customer and increases the likelihood of some of the data becoming stale and incorrect over time. Further, the ability to see a 360 degree view of data for the person is compromised by virtue of not connecting this data under the umbrella of a common person record.

The Anthology domain includes a concept of person centric data with the ability to have a person owning multiple functional roles such as Student and Staff. The goal of this model is to have a single, common place where data about the person is stored or persisted. Thus, when any changes occur for a person regarding name, address, phone numbers, email addresses, etc., the changes only need to be made one time and in one place. When users of Anthology applications are viewing data regarding this person from any context of Student, Staff, Instructor, or any other defined functional role, they will be viewing the same person centric data.

This is a large change that will be achieved through multiple incremental implementation steps. The initial step toward achieving and supporting the Anthology domain model is to prepare the database. Tables need to be created that will store the person centric data. These tables will be automatically populated with existing data in the CampusVue Student tables and exposed in the new query builder of Anthology Student. For now, the Query builder will be the only place where the person centric tables will be exposed. CampusVue Student users who are not licensed for Anthology will have no indication from working inside Anthology Student that these person centric tables exist.

Database Schema Changes

To achieve the business requirement, the following schema changes are introduced in release 16.1:

  • The following new tables are created:

    • SyPerson
    • SyPersonEmail
    • SyPersonAddress
    • SyPersonPhoneNumber

  • The SyPersonId column is introduced in following tables:

    • SyStudent
    • SyStaff
    • SyOrganizationContact
    • PlEmployerContact

Data Model

Database Schema Changes

Click here to view the complete Data Model in a separate PDF file.

Migration Logic

Data from the following tables is migrated to new person related tables.

  • SyStudent
  • SyStaff
  • SyOrganizationContact
  • PlEmployerContact

In addition, SyPersonAddress is populated from the additional SyAddress table.

The rows in each of these tables are copied to the SyPerson record.

Duplicate Logic for SSNs

During the migration, simple duplicate logic is applied. This is based on a valid SSN (Social Security Number).

  • If no SSN is associated with any of the functional role records, a new person record is created.

  • If a valid SSN is associated with any of the functional role records, the migration script checks if a SyPerson record already exists with the same SSN.

  • If a SyPerson record exists, the functional role record is associated with the existing person record.

The SSN will be considered valid if the following conditions are satisfied:

  • The SSN format is: XXX-XX-XXXX

  • The SSN does not start with 999 or 777 or 888 or 000.

If the SSN satisfies these criteria, the record will be associated with the same SyPerson record.

Example

An existing CampusVue Student database contains a person who has three records in the SyStudent table. The same person also is a staff member and has one record in the SyStaff table.

Table 1
SyStudentId FirstName LastName SSN Table
100 John Miller 123-456-7890 SyStudent
8000 Johnathan Miller 123-456-7890 SyStudent
12000 Johnny Miller 123-456-7890 SyStudent
 
6000 J. Miller 123-456-7890 SyStaff

After the migration of data to the person centric tables, one row will exist in SyPerson. The three SyStudent and one SyStaff records will be associated with the same SyPerson record (see Table 2).

Since the first name is represented differently in the existing records (see Table 1), the first name in the record that is migrated last will used to populate the first name in the SyPerson record.

The initial implementation (release 16.1) does not include logic to handle instances with discrepancies in data for different functional role records (i.e., SyStudent, SyStaff, etc.) that contain the same SSN.

Table 2
SyStudentId SyPersonId SSN Table
100 1000052 123-456-7890 SyStudent
8000 1000052 123-456-7890 SyStudent
12000 1000052 123-456-7890 SyStudent
 
6000 1000052 123-456-7890 SyStudent

Modified Insert/Update Triggers

After successful migration and installation, the records in person related tables are maintained using insert/update triggers. The following triggers are modified to support the schema change:

  • trg_SyStaff_Update
  • Trg_SyStaff_Ins
  • Trg_SyOrganizationContact_Upd
  • Trg_SyOrganizationContact_Ins
  • Trg_PlEmployerContact_Upd
  • Trg_PlEmployerContact_Ins
  • syStudent_Upd_trg
  • syStudent_Ins_trg
  • trg_SyAddress_Insert
  • trg_SyAddress_Update

CVueSourceTypeId is used to identify the specific place in the existing CampusVue Student tables where the data came from. This is primarily used to account for the absence of certain planned implementations in Anthology around supporting address types, phone number types, and email address types. Additionally, the CVueSourceTypeId allows for the trigger logic to know precisely which person centric record should be updated when address, phone number, or email address data is changed in CampusVue Student. Table 3 shows the enumerations that are implemented:

Table 3
TableName Enumeration
SyPersonPhoneNumbers
  1. Phone
  2. SyStudent.Work
  3. SyStudent.OtherPhone
  4. Systudent.Mobilenumber
  5. SyStaff.HomePhone
  6. SyStaff.CellPhone
  7. SyStaff.WorkFax
  8. PlEmployerContact.Fax
  9. PlEmployerContact.Phone2
  10. SyOraganizationContact.Fax
  11. SyOraganizationContact.Phone2    
SyPersonAddress
  1. StaffAddress
  2. StaffWorkAddress
  3. SyAddress
  4. SyStudentAddress
  5. PlEmployerContact
  6. SyOrganizationContact
SyPersonEmail
  1. Email
  2. Other
  3. ReplyTo