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
AcademicWeek	SchoolId	CalendarDate	FK_AcademicWeek_CalendarDate1
AcademicWeek	SchoolId	School	FK_AcademicWeek_School
BellScheduleMeetingTime	SchoolId	BellSchedule	FK_BellScheduleMeetingTime_BellSchedule
BellScheduleMeetingTime	SchoolId	ClassPeriod	FK_BellScheduleMeetingTime_ClassPeriod
CourseOffering	SchoolId	School	FK_CourseOffering_School
CourseOffering	SchoolId	Session	FK_CourseOffering_Session
Grade	SchoolId	GradingPeriod	FK_Grade_GradingPeriod
Grade	SchoolId	StudentSectionAssociation	FK_Grade_StudentSectionAssociation
GradebookEntry	SchoolId	GradingPeriod	FK_GradebookEntry_GradingPeriod
GradebookEntry	SchoolId	Section	FK_GradebookEntry_Section
ObjectiveAssessment	AssessmentIdentifier	Assessment	FK_ObjectiveAssessment_Assessment
ObjectiveAssessment	AssessmentIdentifier	ObjectiveAssessment	FK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessment	Namespace	Assessment	FK_ObjectiveAssessment_Assessment
ObjectiveAssessment	Namespace	ObjectiveAssessment	FK_ObjectiveAssessment_ObjectiveAssessment
ObjectiveAssessmentAssessmentItem	AssessmentIdentifier	AssessmentItem	FK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItem	AssessmentIdentifier	ObjectiveAssessment	FK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ObjectiveAssessmentAssessmentItem	Namespace	AssessmentItem	FK_ObjectiveAssessmentAssessmentItem_AssessmentItem
ObjectiveAssessmentAssessmentItem	Namespace	ObjectiveAssessment	FK_ObjectiveAssessmentAssessmentItem_ObjectiveAssessment
ReportCardGrade	GradingPeriodBeginDate	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	GradingPeriodBeginDate	ReportCard	FK_ReportCardGrade_ReportCard
ReportCardGrade	GradingPeriodDescriptorId	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	GradingPeriodDescriptorId	ReportCard	FK_ReportCardGrade_ReportCard
ReportCardGrade	SchoolId	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	SchoolId	ReportCard	FK_ReportCardGrade_ReportCard
ReportCardGrade	StudentUSI	Grade	FK_ReportCardGrade_Grade
ReportCardGrade	StudentUSI	ReportCard	FK_ReportCardGrade_ReportCard
ReportCardStudentCompetencyObjective	GradingPeriodBeginDate	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjective	GradingPeriodBeginDate	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjective	GradingPeriodDescriptorId	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjective	GradingPeriodDescriptorId	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjective	SchoolId	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjective	SchoolId	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentCompetencyObjective	StudentUSI	ReportCard	FK_ReportCardStudentCompetencyObjective_ReportCard
ReportCardStudentCompetencyObjective	StudentUSI	StudentCompetencyObjective	FK_ReportCardStudentCompetencyObjective_StudentCompetencyObjective
ReportCardStudentLearningObjective	GradingPeriodBeginDate	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	GradingPeriodBeginDate	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjective	GradingPeriodDescriptorId	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	GradingPeriodDescriptorId	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjective	SchoolId	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	SchoolId	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective
ReportCardStudentLearningObjective	StudentUSI	ReportCard	FK_ReportCardStudentLearningObjective_ReportCard
ReportCardStudentLearningObjective	StudentUSI	StudentLearningObjective	FK_ReportCardStudentLearningObjective_StudentLearningObjective
Section	SchoolId	ClassPeriod	FK_Section_ClassPeriod
Section	SchoolId	CourseOffering	FK_Section_CourseOffering
Section	SchoolId	Location	FK_Section_Location
Section	SchoolId	School	FK_Section_School
SectionAttendanceTakenEvent	SchoolId	CalendarDate	FK_SectionAttendanceTakenEvent_CalendarDate
SectionAttendanceTakenEvent	SchoolId	Section	FK_SectionAttendanceTakenEvent_Section
SessionAcademicWeek	SchoolId	AcademicWeek	FK_SessionAcademicWeek_AcademicWeek
SessionAcademicWeek	SchoolId	Session	FK_SessionAcademicWeek_Session
SessionGradingPeriod	SchoolId	GradingPeriod	FK_SessionGradingPeriod_GradingPeriod
SessionGradingPeriod	SchoolId	Session	FK_SessionGradingPeriod_Session
StaffEducationOrganizationAssignmentAssociation	StaffUSI	Staff	FK_StaffEducationOrganizationAssignmentAssociation_Staff
StaffEducationOrganizationAssignmentAssociation	StaffUSI	StaffEducationOrganizationEmploymentAssociation	FK_StaffEducationOrganizationAssignmentAssociation_StaffEducationOrganizationEmploymentAssociation
StudentAcademicRecordReportCard	EducationOrganizationId	ReportCard	FK_StudentAcademicRecordReportCard_ReportCard
StudentAcademicRecordReportCard	EducationOrganizationId	StudentAcademicRecord	FK_StudentAcademicRecordReportCard_StudentAcademicRecord
StudentAcademicRecordReportCard	StudentUSI	ReportCard	FK_StudentAcademicRecordReportCard_ReportCard
StudentAcademicRecordReportCard	StudentUSI	StudentAcademicRecord	FK_StudentAcademicRecordReportCard_StudentAcademicRecord
StudentAssessmentItem	AssessmentIdentifier	AssessmentItem	FK_StudentAssessmentItem_AssessmentItem
StudentAssessmentItem	AssessmentIdentifier	StudentAssessment	FK_StudentAssessmentItem_StudentAssessment
StudentAssessmentItem	Namespace	AssessmentItem	FK_StudentAssessmentItem_AssessmentItem
StudentAssessmentItem	Namespace	StudentAssessment	FK_StudentAssessmentItem_StudentAssessment
StudentAssessmentStudentObjectiveAssessment	AssessmentIdentifier	ObjectiveAssessment	FK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment
StudentAssessmentStudentObjectiveAssessment	AssessmentIdentifier	StudentAssessment	FK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment
StudentAssessmentStudentObjectiveAssessment	Namespace	ObjectiveAssessment	FK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment
StudentAssessmentStudentObjectiveAssessment	Namespace	StudentAssessment	FK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment
StudentCompetencyObjective	BeginDate	StudentProgramAssociation	FK_StudentCompetencyObjective_StudentProgramAssociation
StudentCompetencyObjective	BeginDate	StudentSectionAssociation	FK_StudentCompetencyObjective_StudentSectionAssociation
StudentCompetencyObjective	SchoolId	GradingPeriod	FK_StudentCompetencyObjective_GradingPeriod
StudentCompetencyObjective	SchoolId	StudentSectionAssociation	FK_StudentCompetencyObjective_StudentSectionAssociation
StudentCompetencyObjective	StudentUSI	Student	FK_StudentCompetencyObjective_Student