Key Structure in the Ed-Fi ODS / API

Introduction

This article provides technical details on the key structure in the Ed-Fi ODS / API.

Contents

Resource IDs

The ODS / API assigns and exposes resource IDs to align with REST conventions. These IDs do not act as a surrogate key in the ODS data store, although uniqueness is enforced.

The following diagram shows Id in CourseOffering table, which stores the resource ID for an offering:

In the API surface, a CourseOffering can be looked up/queried by doing a HTTP GET on a path like: /courseOfferings/d0fd729db6ee4a7bbc989720e4f833f5.

In the returned JSON, the resource ID appears as the id element:


When an element is POSTed (i.e., created in the ODS / API), the resource ID is provided via a HTTP Header. It will look something like:

USIs (Unique Surrogate Identifiers)

While most of the primary keys in ODS / API are natural keys (i.e., not surrogate keys or identity columns), the Student, Parent, and Staff entities have the integer-based surrogate key columns StudentUSI, PersonUSI, and StaffUSI. These columns contain a primary key, while the text-based StudentUniqueId, ParentUniqueId, and StaffUniqueId natural key columns form unique indexes.

USI surrogates were introduced due to performance considerations with primary indexes on non-numeric columns, and for the advantage they provide when the natural key needs to be updated in these pivotal entities. With the USI surrogate in place, the respective UniqueId field can be updated without having to cascade updates through all related entities. See When Key Values Change: Cascading Updates for more details on cascading updates.

A key point with USIs is that integration with the ODS / API is done via the text-based UniqueId. The integer-based USI is not visible to the API client and is used only as internal identifier.

The following diagram shows the StaffUSI primary key field and the StaffUniqueId field in the Staff table:

Natural Keys

The Ed-Fi ODS / API uses natural keys as its primary means of enforcing uniqueness in records and maintaining relationships between records.

The data in the Ed-Fi ODS / API represents a rich domain with deep relationships. The ODS data store data model is organized into aggregates based on the principles of Domain-Driven Design, and those aggregates are exposed as API resources. Since the ODS is not the system of record (i.e., it is not the system that creates, manages, and is the authoritative source for the data), primary keys are formed from the well-known, natural keys in the domain.

A Natural Key Example Using Bell Schedule Meeting Time

This section provides an example of how natural keys in the ODS / API work in practice. Consider the following diagram, noting the composite key structure of BellScheduleClassPeriod:

While SchoolId is present as a key in both BellSchedule and ClassPeriod, there is only one SchoolId on the BellScheduleClassPeriod. This process of “merging” the SchoolId column from the two keys is known as key unification. In turn, key unification is fundamentally what gives the composite key design approach its power to enforce referential integrity deeply throughout the entire data store — even when data is supplied from many different client systems of record that do not communicate with each other.

When Key Values Change: Cascading Updates

Natural keys are a good solution for the ODS / API because they solve the problem of maintaining referential integrity throughout a deep data structure maintained by client systems that are disconnected from each other. However, natural keys come with an inherent challenge in that a change in a primary key value has the potential to cascade through the primary keys of many child tables. Wherever possible, the ODS / API uses natural keys that are stable and unlikely to change — but change does occur in the real world.

An approach to deal with this challenge is to turn on the CASCADE UPDATE option on all tables and let the database server handle it. Currently, the as-shipped Ed-Fi ODS enables this behavior on selected entities such as Class Period, Grade, Section, and so forth.

The article Cascading Key Updates on ODS / API Resources has the full list of entities covered in the as-shipped configuration as well as steps for enabling CASCADE UPDATE on additional entities.

Key Unification Report

Developers and database administrators may find the query in this section of interest.

The query below identifies all locations within the Ed-Fi ODS data store where key unification is occurring. The FOREIGN_TABLE_NAME and FOREIGN_COLUMN_NAME columns represent the tables and columns where key unification is occurring (and consequently, the FOREIGN_COLUMN_NAME values will always appear as identical values in adjacent pairs of records).

WITH ForeignKeys AS
(
-- Key column usage Primary/Foreign tables
SELECT KCU_FK.CONSTRAINT_NAME,
               KCU.TABLE_SCHEMA PRIMARY_TABLE_SCHEMA, 
               KCU.TABLE_NAME PRIMARY_TABLE_NAME, 
               KCU.ORDINAL_POSITION, 
               KCU.COLUMN_NAME PRIMARY_COLUMN_NAME, 
               KCU_FK.TABLE_SCHEMA FOREIGN_TABLE_SCHEMA, 
               KCU_FK.TABLE_NAME FOREIGN_TABLE_NAME, 
               KCU_FK.COLUMN_NAME FOREIGN_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
               ON KCU.CONSTRAINT_CATALOG=TC.CONSTRAINT_CATALOG
                       AND KCU.CONSTRAINT_SCHEMA=TC.CONSTRAINT_SCHEMA
                       AND KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
               ON TC.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
                       AND TC.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
                       AND TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
        JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK
               ON RC.CONSTRAINT_CATALOG = KCU_FK.CONSTRAINT_CATALOG
                       AND RC.CONSTRAINT_SCHEMA = KCU_FK.CONSTRAINT_SCHEMA
                       AND RC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
               AND KCU.ORDINAL_POSITION = KCU_FK.ORDINAL_POSITION
WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
    AND KCU.CONSTRAINT_SCHEMA IN ('edfi', 'extension')
    AND KCU_FK.CONSTRAINT_SCHEMA IN ('edfi', 'extension')
),
UnifiedKeyColumns AS
(
SELECT FOREIGN_TABLE_NAME, FOREIGN_COLUMN_NAME, COUNT(*) AS KeyUnificationSources
FROM   ForeignKeys
GROUP BY FOREIGN_TABLE_NAME, FOREIGN_COLUMN_NAME
HAVING COUNT(*) > 1
)
 
SELECT fks.FOREIGN_TABLE_NAME, fks.FOREIGN_COLUMN_NAME, fks.PRIMARY_TABLE_NAME, fks.CONSTRAINT_NAME
FROM   ForeignKeys fks 
               INNER JOIN UnifiedKeyColumns ukc 
                       ON fks.FOREIGN_TABLE_NAME = ukc.FOREIGN_TABLE_NAME
                              AND fks.FOREIGN_COLUMN_NAME = ukc.FOREIGN_COLUMN_NAME
-- Criteria for Section related key unification
ORDER BY FOREIGN_TABLE_NAME, FOREIGN_COLUMN_NAME


Unified Keys in the As-Shipped Ed-Fi ODS

Running the SQL Query above on the as-shipped ODS data store results in the following report:

FOREIGN_TABLE_NAMEFOREIGN_COLUMN_NAMEPRIMARY_TABLE_NAMECONSTRAINT_NAME
AccountAccountCodeEducationOrganizationIdAccountFK_AccountAccountCode_Account
AccountAccountCodeEducationOrganizationIdAccountCodeFK_AccountAccountCode_AccountCode
AccountAccountCodeFiscalYearAccountFK_AccountAccountCode_Account
AccountAccountCodeFiscalYearAccountCodeFK_AccountAccountCode_AccountCode
BellScheduleClassPeriodSchoolIdBellScheduleFK_BellScheduleClassPeriod_BellSchedule
BellScheduleClassPeriodSchoolIdClassPeriodFK_BellScheduleClassPeriod_ClassPeriod
CourseOfferingSchoolIdSchoolFK_CourseOffering_School
CourseOfferingSchoolIdSessionFK_CourseOffering_Session
DisciplineActionStudentDisciplineIncidentAssociationStudentUSIDisciplineActionFK_DisciplineActionStudentDisciplineIncidentAssociation_DisciplineAction
DisciplineActionStudentDisciplineIncidentAssociationStudentUSIStudentDisciplineIncidentAssociationFK_DisciplineActionStudentDisciplineIncidentAssociation_StudentDisciplineIncidentAssociation
GradeSchoolIdGradingPeriodFK_Grade_GradingPeriod
GradeSchoolIdStudentSectionAssociationFK_Grade_StudentSectionAssociation
GradebookEntrySchoolIdGradingPeriodFK_GradebookEntry_GradingPeriod
GradebookEntrySchoolIdSectionFK_GradebookEntry_Section
GradebookEntrySchoolYearGradingPeriodFK_GradebookEntry_GradingPeriod
GradebookEntrySchoolYearSectionFK_GradebookEntry_Section
ObjectiveAssessmentAcademicSubjectDescriptorIdAssessmentFK_ObjectiveAssessment_Assessment
ObjectiveAssessmentAcademicSubjectDescriptorIdObjectiveAssessmentFK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessmentAssessedGradeLevelDescriptorIdAssessmentFK_ObjectiveAssessment_Assessment
ObjectiveAssessmentAssessedGradeLevelDescriptorIdObjectiveAssessmentFK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessmentAssessmentTitleAssessmentFK_ObjectiveAssessment_Assessment
ObjectiveAssessmentAssessmentTitleObjectiveAssessmentFK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessmentAssessmentVersionAssessmentFK_ObjectiveAssessment_Assessment
ObjectiveAssessmentAssessmentVersionObjectiveAssessmentFK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessmentAssessmentItemAcademicSubjectDescriptorIdAssessmentItemFK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItemAcademicSubjectDescriptorIdObjectiveAssessmentFK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItemAssessedGradeLevelDescriptorIdAssessmentItemFK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItemAssessedGradeLevelDescriptorIdObjectiveAssessmentFK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItemAssessmentTitleAssessmentItemFK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItemAssessmentTitleObjectiveAssessmentFK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItemAssessmentVersionAssessmentItemFK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItemAssessmentVersionObjectiveAssessmentFK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentLearningObjectiveAcademicSubjectDescriptorIdLearningObjectiveFK_ObjectiveAssessmentLearningObjective_LearningObjective
ObjectiveAssessmentLearningObjectiveAcademicSubjectDescriptorIdObjectiveAssessmentFK_ObjectiveAssessmentLearningObjective_ObjectiveAssessment
ObjectiveAssessmentLearningObjectiveAssessedGradeLevelDescriptorIdLearningObjectiveFK_ObjectiveAssessmentLearningObjective_LearningObjective
ObjectiveAssessmentLearningObjectiveAssessedGradeLevelDescriptorIdObjectiveAssessmentFK_ObjectiveAssessmentLearningObjective_ObjectiveAssessment
ReportCardGradeGradingPeriodDescriptorIdGradeFK_ReportCardGrade_Grade
ReportCardGradeGradingPeriodDescriptorIdReportCardFK_ReportCardGrade_ReportCard
ReportCardGradeGradingPeriodSchoolYearGradeFK_ReportCardGrade_Grade
ReportCardGradeGradingPeriodSchoolYearReportCardFK_ReportCardGrade_ReportCard
ReportCardGradeGradingPeriodSequenceGradeFK_ReportCardGrade_Grade
ReportCardGradeGradingPeriodSequenceReportCardFK_ReportCardGrade_ReportCard
ReportCardGradeStudentUSIGradeFK_ReportCardGrade_Grade
ReportCardGradeStudentUSIReportCardFK_ReportCardGrade_ReportCard
ReportCardStudentCompetencyObjectiveGradingPeriodDescriptorIdReportCardFK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjectiveGradingPeriodDescriptorIdStudentCompetencyObjectiveFK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjectiveGradingPeriodSchoolIdReportCardFK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjectiveGradingPeriodSchoolIdStudentCompetencyObjectiveFK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjectiveGradingPeriodSchoolYearReportCardFK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjectiveGradingPeriodSchoolYearStudentCompetencyObjectiveFK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjectiveGradingPeriodSequenceReportCardFK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjectiveGradingPeriodSequenceStudentCompetencyObjectiveFK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjectiveStudentUSIReportCardFK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjectiveStudentUSIStudentCompetencyObjectiveFK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentLearningObjectiveGradingPeriodDescriptorIdReportCardFK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjectiveGradingPeriodDescriptorIdStudentLearningObjectiveFK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjectiveGradingPeriodSchoolIdReportCardFK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjectiveGradingPeriodSchoolIdStudentLearningObjectiveFK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjectiveGradingPeriodSchoolYearReportCardFK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjectiveGradingPeriodSchoolYearStudentLearningObjectiveFK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjectiveGradingPeriodSequenceReportCardFK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjectiveGradingPeriodSequenceStudentLearningObjectiveFK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjectiveStudentUSIReportCardFK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjectiveStudentUSIStudentLearningObjectiveFK_ReportCardStudentLearningObjective_StudentLearningObjective
SectionLocationSchoolIdLocationFK_Section_Location
SectionLocationSchoolIdSchoolFK_Section_School
SectionAttendanceTakenEventSchoolIdCalendarDateFK_SectionAttendanceTakenEvent_CalendarDate
SectionAttendanceTakenEventSchoolIdSectionFK_SectionAttendanceTakenEvent_Section
SectionAttendanceTakenEventSchoolYearCalendarDateFK_SectionAttendanceTakenEvent_CalendarDate
SectionAttendanceTakenEventSchoolYearSectionFK_SectionAttendanceTakenEvent_Section
SectionClassPeriodSchoolIdClassPeriodFK_SectionClassPeriod_ClassPeriod
SectionClassPeriodSchoolIdSectionFK_SectionClassPeriod_Section
SessionAcademicWeekSchoolIdAcademicWeekFK_SessionAcademicWeek_AcademicWeek
SessionAcademicWeekSchoolIdSessionFK_SessionAcademicWeek_Session
SessionGradingPeriodSchoolIdGradingPeriodFK_SessionGradingPeriod_GradingPeriod
SessionGradingPeriodSchoolIdSessionFK_SessionGradingPeriod_Session
SessionGradingPeriodSchoolYearGradingPeriodFK_SessionGradingPeriod_GradingPeriod
SessionGradingPeriodSchoolYearSessionFK_SessionGradingPeriod_Session
StaffEducationOrganizationAssignmentAssociationStaffUSIStaffFK_StaffEducationOrganizationAssignmentAssociation_Staff
StaffEducationOrganizationAssignmentAssociationStaffUSIStaffEducationOrganizationEmploymentAssociationFK_StaffEducationOrganizationAssignmentAssociation_StaffEducationOrganizationEmploymentAssociation
StaffSchoolAssociationSchoolIdCalendarFK_StaffSchoolAssociation_Calendar
StaffSchoolAssociationSchoolIdSchoolFK_StaffSchoolAssociation_School
StaffSchoolAssociationSchoolYearCalendarFK_StaffSchoolAssociation_Calendar
StaffSchoolAssociationSchoolYearSchoolYearTypeFK_StaffSchoolAssociation_SchoolYearType
StudentAcademicRecordReportCardEducationOrganizationIdReportCardFK_StudentAcademicRecordReportCard_ReportCard
StudentAcademicRecordReportCardEducationOrganizationIdStudentAcademicRecordFK_StudentAcademicRecordReportCard_StudentAcademicRecord
StudentAcademicRecordReportCardStudentUSIReportCardFK_StudentAcademicRecordReportCard_ReportCard
StudentAcademicRecordReportCardStudentUSIStudentAcademicRecordFK_StudentAcademicRecordReportCard_StudentAcademicRecord
StudentAssessmentItemAcademicSubjectDescriptorIdAssessmentItemFK_StudentAssessmentItem_AssessmentItem
StudentAssessmentItemAcademicSubjectDescriptorIdStudentAssessmentFK_StudentAssessmentItem_StudentAssessment
StudentAssessmentItemAssessedGradeLevelDescriptorIdAssessmentItemFK_StudentAssessmentItem_AssessmentItem
StudentAssessmentItemAssessedGradeLevelDescriptorIdStudentAssessmentFK_StudentAssessmentItem_StudentAssessment
StudentAssessmentItemAssessmentTitleAssessmentItemFK_StudentAssessmentItem_AssessmentItem
StudentAssessmentItemAssessmentTitleStudentAssessmentFK_StudentAssessmentItem_StudentAssessment
StudentAssessmentItemAssessmentVersionAssessmentItemFK_StudentAssessmentItem_AssessmentItem
StudentAssessmentItemAssessmentVersionStudentAssessmentFK_StudentAssessmentItem_StudentAssessment
StudentAssessmentStudentObjectiveAssessmentAcademicSubjectDescriptorIdObjectiveAssessmentFK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment
StudentAssessmentStudentObjectiveAssessmentAcademicSubjectDescriptorIdStudentAssessmentFK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment
StudentAssessmentStudentObjectiveAssessmentAssessedGradeLevelDescriptorIdObjectiveAssessmentFK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment
StudentAssessmentStudentObjectiveAssessmentAssessedGradeLevelDescriptorIdStudentAssessmentFK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment
StudentAssessmentStudentObjectiveAssessmentAssessmentTitleObjectiveAssessmentFK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment
StudentAssessmentStudentObjectiveAssessmentAssessmentTitleStudentAssessmentFK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment
StudentAssessmentStudentObjectiveAssessmentAssessmentVersionObjectiveAssessmentFK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment
StudentAssessmentStudentObjectiveAssessmentAssessmentVersionStudentAssessmentFK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment
StudentCompetencyObjectiveGeneralStudentProgramAssociationStudentUSIGeneralStudentProgramAssociationFK_StudentCompetencyObjectiveGeneralStudentProgramAssociation_GeneralStudentProgramAssociation
StudentCompetencyObjectiveGeneralStudentProgramAssociationStudentUSIStudentCompetencyObjectiveFK_StudentCompetencyObjectiveGeneralStudentProgramAssociation_StudentCompetencyObjective
StudentCompetencyObjectiveStudentSectionAssociationStudentUSIStudentCompetencyObjectiveFK_StudentCompetencyObjectiveStudentSectionAssociation_StudentCompetencyObjective
StudentCompetencyObjectiveStudentSectionAssociationStudentUSIStudentSectionAssociationFK_StudentCompetencyObjectiveStudentSectionAssociation_StudentSectionAssociation
StudentGradebookEntryLocalCourseCodeGradebookEntryFK_StudentGradebookEntry_GradebookEntry
StudentGradebookEntryLocalCourseCodeStudentSectionAssociationFK_StudentGradebookEntry_StudentSectionAssociation
StudentGradebookEntrySchoolIdGradebookEntryFK_StudentGradebookEntry_GradebookEntry
StudentGradebookEntrySchoolIdStudentSectionAssociationFK_StudentGradebookEntry_StudentSectionAssociation
StudentGradebookEntrySchoolYearGradebookEntryFK_StudentGradebookEntry_GradebookEntry
StudentGradebookEntrySchoolYearStudentSectionAssociationFK_StudentGradebookEntry_StudentSectionAssociation
StudentGradebookEntrySectionIdentifierGradebookEntryFK_StudentGradebookEntry_GradebookEntry
StudentGradebookEntrySectionIdentifierStudentSectionAssociationFK_StudentGradebookEntry_StudentSectionAssociation
StudentGradebookEntrySessionNameGradebookEntryFK_StudentGradebookEntry_GradebookEntry
StudentGradebookEntrySessionNameStudentSectionAssociationFK_StudentGradebookEntry_StudentSectionAssociation
StudentLearningObjectiveGeneralStudentProgramAssociationStudentUSIGeneralStudentProgramAssociationFK_StudentLearningObjectiveGeneralStudentProgramAssociation_GeneralStudentProgramAssociation
StudentLearningObjectiveGeneralStudentProgramAssociationStudentUSIStudentLearningObjectiveFK_StudentLearningObjectiveGeneralStudentProgramAssociation_StudentLearningObjective
StudentLearningObjectiveStudentSectionAssociationStudentUSIStudentLearningObjectiveFK_StudentLearningObjectiveStudentSectionAssociation_StudentLearningObjective
StudentLearningObjectiveStudentSectionAssociationStudentUSIStudentSectionAssociationFK_StudentLearningObjectiveStudentSectionAssociation_StudentSectionAssociation
StudentSchoolAssociationSchoolIdCalendarFK_StudentSchoolAssociation_Calendar
StudentSchoolAssociationSchoolIdSchoolFK_StudentSchoolAssociation_School
StudentSchoolAssociationSchoolYearCalendarFK_StudentSchoolAssociation_Calendar
StudentSchoolAssociationSchoolYearSchoolYearTypeFK_StudentSchoolAssociation_SchoolYearType
StudentSchoolAttendanceEventSchoolIdSchoolFK_StudentSchoolAttendanceEvent_School
StudentSchoolAttendanceEventSchoolIdSessionFK_StudentSchoolAttendanceEvent_Session