Key Structure in the Ed-Fi ODS / API

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., the system that creates and manages the data), primary keys are formed from the well-known, natural keys in the domain.

The ODS / API also assigns and exposes resource IDs, but those IDs do not act as a surrogate key.

A Natural Key Example using Bell Schedule Meeting Time

This section provides an example of how this works in practice. Consider the following diagram, noting the composite key structure of BellScheduleMeetingTime:

While SchoolId migrates in from both of the foreign keys from BellSchedule and ClassPeriod, there is only one SchoolId on the BellScheduleMeetingTime. 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 How To: Enable Cascading Updates on Ed-Fi 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_NAME	FOREIGN_COLUMN_NAME	PRIMARY_TABLE_NAME	CONSTRAINT_NAME

FOREIGN_TABLE_NAME	FOREIGN_COLUMN_NAME	PRIMARY_TABLE_NAME	CONSTRAINT_NAME
AcademicWeek	SchoolId	CalendarDate	FK_AcademicWeek_CalendarDate_BeginDate
AcademicWeek	SchoolId	CalendarDate	FK_AcademicWeek_CalendarDate_EndDate
AcademicWeek	SchoolId	School	FK_AcademicWeek_School_SchoolId
BellScheduleMeetingTime	SchoolId	BellSchedule	FK_BellScheduleMeetingTime_BellSchedule
BellScheduleMeetingTime	SchoolId	ClassPeriod	FK_BellScheduleMeetingTime_ClassPeriod
CourseOffering	SchoolId	School	FK_CourseOffering_School_SchoolId
CourseOffering	SchoolId	Session	FK_CourseOffering_Session_SchoolId
Grade	SchoolId	GradingPeriod	FK_Grade_GradingPeriod_BeginDate
Grade	SchoolId	StudentSectionAssociation	FK_Grade_StudentSectionAssociation
GradebookEntry	SchoolId	GradingPeriod	FK_GradebookEntry_GradingPeriod
GradebookEntry	SchoolId	Section	FK_GradebookEntry_Section_SchoolId
ObjectiveAssessment	AcademicSubjectDescriptorId	Assessment	FK_ObjectiveAssessment_Assessment_AssessmentTitle
ObjectiveAssessment	AcademicSubjectDescriptorId	ObjectiveAssessment	FK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessment	AssessedGradeLevelDescriptorId	Assessment	FK_ObjectiveAssessment_Assessment_AssessmentTitle
ObjectiveAssessment	AssessedGradeLevelDescriptorId	ObjectiveAssessment	FK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessment	AssessmentTitle	Assessment	FK_ObjectiveAssessment_Assessment_AssessmentTitle
ObjectiveAssessment	AssessmentTitle	ObjectiveAssessment	FK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessment	Version	Assessment	FK_ObjectiveAssessment_Assessment_AssessmentTitle
ObjectiveAssessment	Version	ObjectiveAssessment	FK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessmentAssessmentItem	AcademicSubjectDescriptorId	AssessmentItem	FK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItem	AcademicSubjectDescriptorId	ObjectiveAssessment	FK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItem	AssessedGradeLevelDescriptorId	AssessmentItem	FK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItem	AssessedGradeLevelDescriptorId	ObjectiveAssessment	FK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItem	AssessmentTitle	AssessmentItem	FK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItem	AssessmentTitle	ObjectiveAssessment	FK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItem	Version	AssessmentItem	FK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItem	Version	ObjectiveAssessment	FK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentLearningObjective	AcademicSubjectDescriptorId	LearningObjective	FK_ObjectiveAssessmentLearningObjective_LearningObjective_Objective
ObjectiveAssessmentLearningObjective	AcademicSubjectDescriptorId	ObjectiveAssessment	FK_ObjectiveAssessmentLearningObjective_ObjectiveAssessment
ObjectiveAssessmentLearningObjective	AssessedGradeLevelDescriptorId	LearningObjective	FK_ObjectiveAssessmentLearningObjective_LearningObjective_Objective
ObjectiveAssessmentLearningObjective	AssessedGradeLevelDescriptorId	ObjectiveAssessment	FK_ObjectiveAssessmentLearningObjective_ObjectiveAssessment
ReportCardGrade	GradingPeriodBeginDate	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	GradingPeriodBeginDate	ReportCard	FK_ReportCardGrade_ReportCard_SchoolId
ReportCardGrade	GradingPeriodDescriptorId	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	GradingPeriodDescriptorId	ReportCard	FK_ReportCardGrade_ReportCard_SchoolId
ReportCardGrade	SchoolId	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	SchoolId	ReportCard	FK_ReportCardGrade_ReportCard_SchoolId
ReportCardGrade	StudentUSI	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	StudentUSI	ReportCard	FK_ReportCardGrade_ReportCard_SchoolId
ReportCardStudentCompetencyObjective	GradingPeriodBeginDate	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard_StudentUSI
ReportCardStudentCompetencyObjective	GradingPeriodBeginDate	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective_StudentUSI
ReportCardStudentCompetencyObjective	GradingPeriodDescriptorId	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard_StudentUSI
ReportCardStudentCompetencyObjective	GradingPeriodDescriptorId	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective_StudentUSI
ReportCardStudentCompetencyObjective	SchoolId	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard_StudentUSI
ReportCardStudentCompetencyObjective	SchoolId	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective_StudentUSI
ReportCardStudentCompetencyObjective	StudentUSI	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard_StudentUSI
ReportCardStudentCompetencyObjective	StudentUSI	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective_StudentUSI
ReportCardStudentLearningObjective	GradingPeriodBeginDate	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	GradingPeriodBeginDate	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective_StudentUSI
ReportCardStudentLearningObjective	GradingPeriodDescriptorId	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	GradingPeriodDescriptorId	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective_StudentUSI
ReportCardStudentLearningObjective	SchoolId	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	SchoolId	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective_StudentUSI
ReportCardStudentLearningObjective	StudentUSI	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	StudentUSI	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective_StudentUSI
Section	SchoolId	ClassPeriod	FK_Section_ClassPeriod_SchoolId
Section	SchoolId	CourseOffering	FK_Section_CourseOffering_SchoolId
Section	SchoolId	Location	FK_Section_Location_SchoolId
Section	SchoolId	School	FK_Section_School_SchoolId
SectionAttendanceTakenEvent	SchoolId	Section	FK_SectionAttendanceTakeEvent_Section_SchoolId
SectionAttendanceTakenEvent	SchoolId	CalendarDate	FK_SectionAttendanceTakenEvent_CalendarDate_SchoolId
SessionAcademicWeek	SchoolId	AcademicWeek	FK_AcademicWeek_Session_SchoolId
SessionAcademicWeek	SchoolId	Session	FK_SessionAcademicWeek_Session_SchoolId
SessionGradingPeriod	SchoolId	GradingPeriod	FK_SessionGradingPeriod_GradingPeriod_SchoolId
SessionGradingPeriod	SchoolId	Session	FK_SessionGradingPeriod_Session_SchoolId
StaffEducationOrganization-AssignmentAssociation	StaffUSI	StaffEducationOrganization-EmploymentAssociation	FK_StaffEducationOrganizationAssignmentAssociation _StaffEducationOrganizationEmploymentAssociation_StaffUSI
StaffEducationOrganization-AssignmentAssociation	StaffUSI	Staff	FK_StaffEducationOrgAssignmentAssociation_Staff_StaffUSI
StudentAcademicRecordReportCard	EducationOrganizationId	ReportCard	FK_StudentAcademicRecordReportCard_ReportCard
StudentAcademicRecordReportCard	EducationOrganizationId	StudentAcademicRecord	FK_StudentAcademicRecordReportCard_StudentAcademicRecord_StudentUSI
StudentAcademicRecordReportCard	StudentUSI	ReportCard	FK_StudentAcademicRecordReportCard_ReportCard
StudentAcademicRecordReportCard	StudentUSI	StudentAcademicRecord	FK_StudentAcademicRecordReportCard_StudentAcademicRecord_StudentUSI
StudentAssessmentItem	AcademicSubjectDescriptorId	AssessmentItem