search

CEDStandards / CEDS-IDS

The CEDS Integrated Data Store factors the entities and attributes of the CEDS Domain Entity Schema (DES) with standard technical syntax and 3rd normal form database normalization. The IDS Logical Model provides a standard framework for integration of P-20 data systems through a well-normalized “operational data store”. In a P-20 data system, the IDS models the most current view of data available to the enterprise, including some historical data (such as prior assessment data and enrollment records). A “record” is generally added for each Person-Organization Relationship change event in the system. This CEDS logical model does not directly address the production aspects of log and change management.
https://github.com/CEDStandards/CEDS-IDS/tree/master/doc
Apache License 2.0
42 stars 17 forks source link

Duplication of OrganizationID in Course and CourseSection #74

Open VT-AOE-DMAD-Drew-Bennett opened 8 months ago

VT-AOE-DMAD-Drew-Bennett commented 8 months ago

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 commented 6 months ago

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.