Duplication of OrganizationID in Course and CourseSection

[VT-AOE-DMAD-Drew-Bennett]

Author(s)
Drew Bennett-Stein

Authoring Organization(s)
State of Vermont

Email Address
drew.bennett@vermont.gov

Use Case Title
"Resolving OrganizationID Duplication in Course and CourseSection Components"

Use Case Overview
This use case addresses the issue of duplicate OrganizationID fields in both the Course and CourseSection data components. The goal is to establish a clear distinction and correct data structure to ensure accurate, non-redundant representation of organizational identifiers across these components.

Pull Request Number(s) (If applicable)
N/A

Use Case Background
The duplication of OrganizationID in Course and CourseSection leads to confusion and potential data integrity issues. This redundancy complicates data mapping and integration processes, necessitating a review and restructuring of these elements to better align with CEDS standards.

Components Affected
CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Element Gap Analysis
[Provide Link to CEDS Align Map or Connection]

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
The proposed change involves the restructuring of the OrganizationID field within the Course and CourseSection components to eliminate duplication and enhance data integrity. A unique identifier system will be introduced to differentiate organizational references in these components.

As-Is Diagram

Proposed To-Be Diagram

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
The data warehouse design will be updated to reflect the changes in the data model, ensuring that the unique identifiers for organizations are accurately represented and utilized in all relevant tables and reports.

As-Is Diagram

Proposed To-Be Diagram

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
The changes will necessitate updates to the existing data migration processes to accommodate the new structure of the OrganizationID fields in the Course and CourseSection components.

New Business Rule
A new rule will be implemented to ensure the uniqueness and non-duplication of OrganizationID across different components.

Existing Business Rule
The current business rule allows for the duplication of OrganizationID in both Course and CourseSection components.

Changed Business Rule
The existing rule will be modified to restrict the use of OrganizationID to ensure distinct and accurate representation of organizational identifiers in these components.

[AEMDuaneBrown]

The OrganizationId in Course and the OrganizationId in CourseSection represent different organizations. In the IDS, a Course is an Organization and must be assigned an OrganizationId. In the IDS, a CourseSection is also an Organization and must be assigned an OrganizationId. The OrganizationId's in Course and CourseSection should always be different. The relationship that exists between a course and a course section is displayed through the use of the OrganizationRelationship table.

Example architecture:
OrganizationId = 1 - represents a school
OrganizationId = 2 - represents a course the school offers
OrganizationId = 3 - represents a course section which is an instance of the course

The OrganizationId column in the table K12School would be 1.
The OrganizationId column in the table Course would be 2.
The OrganizationId column in the table CourseSection would be 3.

To show that the K12School offers the Course, there would be an OrganizationRelationship table that contains the OrganizationId's of 1 and 2.

To show that the CourseSection is an instance of the Course, there would be an OrganizationRelationship table that contains the OrganizationId's of 2 and 3.