OpenStaffPosition primary key contains more columns than necessary to achieve unique identification - data standard analysis

Summary of outcomes for Data Standard 2.1

• Problem Addressed: The primary key of the OpenStaffPosition domain entity contains more columns than necessary to achieve unique identification. The inclusion of a date in the key is overly specific and causes difficulty when creating references.
• High-level Reasoning: The property RequisitionNumber and the reference to the EducationOrganization provide sufficient uniqueness for identity. Including a date (DatePosted) in the key is problematic because one must know the date something was posted to be able to reference it. The other properties currently in the key (EmploymentStatus and StaffClassification) should not be part of the key as they are not necessary to create unique identification. They should, however, continue to be required.
• Changes Made: Changed cardinality of OpenStaffPosition properties StaffClassification, EmploymentStatus, and DatePosted from "is part of identity" to "is required"

Original ticket description follows:

The OpenStaffPosition has a primary key that includes both the "RequisitionNumber" and "DatePosted", which itself is overly specific and causes difficulty with creating references (since you have to know the date something was posted to be able to reference it).

But upon review of the primary key, the requisition number scoped within the EducationOrganizationId is sufficient to uniquely identify the open staff position. The EmploymentStatusDescriptorId (e.g. Contractual, Substitute/temporary, Tenured or permanent) and StaffClassificationDescriptorId (e.g. Principal, School Administrator, School Leader) should actually not be part of the primary key. It appears that perhaps the RequisitionNumber was added after the initial modeling but the rest of the primary key wasn't revisited – the column was simply added.

Generated based on ODS-790 - created as a result of TNTP project needs. Need to evaluate whether updating primary key of OpenStaffPosition makes sense from a data standard modeling perspective.

—

Design

• Remove DatePosted, StaffEmploymentStatus and StaffClassification from the key, but keep them required.

RequisitionNumber is sufficiently unique, given that EdOrgReference is part of the key.

Alternate designs considered

None

Breaking change

Minimal impact - while technically a key change, there should be few impacts to clients.