Skip to end of metadata
Go to start of metadata

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_NAMEPRIMARY_TABLE_NAMECONSTRAINT_NAME
AcademicWeekSchoolIdCalendarDateFK_AcademicWeek_CalendarDate_BeginDate
AcademicWeek

SchoolId

CalendarDate

FK_AcademicWeek_CalendarDate_EndDate

AcademicWeek

SchoolIdSchoolFK_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

FK_StudentAssessmentItem_AssessmentItem

StudentAssessmentItem

AcademicSubjectDescriptorId

StudentAssessment

FK_StudentAssessmentItem_StudentAssessment_StudentUSI

StudentAssessmentItem

AssessedGradeLevelDescriptorId

AssessmentItem

FK_StudentAssessmentItem_AssessmentItem

StudentAssessmentItem

AssessedGradeLevelDescriptorId

StudentAssessment

FK_StudentAssessmentItem_StudentAssessment_StudentUSI

StudentAssessmentItem

AssessmentTitle

AssessmentItem

FK_StudentAssessmentItem_AssessmentItem

StudentAssessmentItem

AssessmentTitle

StudentAssessment

FK_StudentAssessmentItem_StudentAssessment_StudentUSI

StudentAssessmentItem

Version

AssessmentItem

FK_StudentAssessmentItem_AssessmentItem

StudentAssessmentItem

Version

StudentAssessment

FK_StudentAssessmentItem_StudentAssessment_StudentUSI

StudentAssessmentStudentObjectiveAssessment

AcademicSubjectDescriptorId

ObjectiveAssessment

FK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment

StudentAssessmentStudentObjectiveAssessment

AcademicSubjectDescriptorId

StudentAssessment

FK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment_StudentUSI

StudentAssessmentStudentObjectiveAssessment

AssessedGradeLevelDescriptorId

ObjectiveAssessment

FK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment

StudentAssessmentStudentObjectiveAssessment

AssessedGradeLevelDescriptorId

StudentAssessment

FK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment_StudentUSI

StudentAssessmentStudentObjectiveAssessment

AssessmentTitle

ObjectiveAssessment

FK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment

StudentAssessmentStudentObjectiveAssessment

AssessmentTitle

StudentAssessment

FK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment_StudentUSI

StudentAssessmentStudentObjectiveAssessment

Version

ObjectiveAssessment

FK_StudentAssessmentStudentObjectiveAssessment_ObjectiveAssessment

StudentAssessmentStudentObjectiveAssessment

Version

StudentAssessment

FK_StudentAssessmentStudentObjectiveAssessment_StudentAssessment_StudentUSI

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_StudentUSI

StudentCompetencyObjective

StudentUSI

StudentProgramAssociation

FK_StudentCompetencyObjective_StudentProgramAssociation

StudentCompetencyObjective

StudentUSI

StudentSectionAssociation

FK_StudentCompetencyObjective_StudentSectionAssociation

StudentGradebookEntry

ClassPeriodName

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

ClassPeriodName

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

ClassroomIdentificationCode

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

ClassroomIdentificationCode

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

LocalCourseCode

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

LocalCourseCode

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

SchoolId

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

SchoolId

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

SchoolYear

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

SchoolYear

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

SequenceOfCourse

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

SequenceOfCourse

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

TermDescriptorId

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

TermDescriptorId

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentGradebookEntry

UniqueSectionCode

GradebookEntry

FK_StudentGradebookEntry_GradebookEntry_SchoolId

StudentGradebookEntry

UniqueSectionCode

StudentSectionAssociation

FK_StudentGradebookEntry_StudentSectionAssociation_StudentUSI

StudentLearningObjective

SchoolId

GradingPeriod

FK_StudentLearningObjective_GradingPeriod_SchoolId

StudentLearningObjective

SchoolId

StudentSectionAssociation

FK_StudentLearningObjective_StudentSectionAssociation_StudentUSI

StudentLearningObjective

StudentUSI

Student

FK_StudentLearningObjective_Student_StudentUSI

StudentLearningObjective

StudentUSI

StudentProgramAssociation

FK_StudentLearningObjective_StudentProgramAssociation

StudentLearningObjective

StudentUSI

StudentSectionAssociation

FK_StudentLearningObjective_StudentSectionAssociation_StudentUSI

StudentSchoolAttendanceEvent

SchoolId

School

FK_StudentSchoolAttendanceEvent_School_SchoolId

StudentSchoolAttendanceEvent

SchoolId

Session

FK_StudentSchoolAttendanceEvent_Session_SchoolId

 

 

  • No labels