Skip to main content
California Department of Education Logo

AAV of Staff Data - Entity Relationship Diagram

Accessible Alternate Version (AAV) of the Staff Data - Entity Relationship Diagram.

This is an accessible alternate version (AAV) of the Staff Data - Entity Relationship Diagram. The Staff Data - Entity Relationship Diagram(PDF) file should be the version selected by most users.

Tables

Five tables are used in the Staff Data – Entity Relationship Diagram. They are:

  • StaffDemographics
  • StaffSchoolFTE
  • StaffAssignment
  • StaffCredentials
  • CourseEnrollment
  • CoursesTaught
  • ClassEnrollment

Description of the Seven Tables

StaffDemographics Table
  • This table stores one record for every certificated staff member working at an LEA.
  • The fields are: AcademicYear, RecID, DistrictCode, CountyName, DistrictName, GenderCode, Age, EducationLevel, EthnicGroup, YearsTeaching, YearsInDistrict, EmploymentStatusCode, FTE Teaching, FTE Administrative, FTE PupilServices, FileCreated.
  • Primary Key of StaffDemographics is RecID + DistrictCode.
StaffSchoolFTE Table
  • This table stores one record for every staff member, school and job classification.
  • The fields are: AcademicYear, RecID, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, JobClassification, StaffType, FTE, FileCreated.
  • Primary Key of StaffSchoolFTE is RecID + DistrictCode + SchoolCode + JobClassification.
StaffAssignment Table
  • This table stores one record for every staff member, school, ClassID, and course code.
  • The fields are: AcademicYear, RecID, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, StaffType, AssignmentCode, ClassID, CourseCode, EstimatedFTE, FileCreated.
  • Primary Key of StaffAssignment:
    • For Teacher record: RecID + DistrictCode + SchoolCode + ClassID + CourseCode
    • For Administrator or Pupil Services record: RecID + DistrictCode + SchoolCode + AssignmentCode
StaffCredentials Table
  • This table stores one record for every certificated staff member and credential.
  • The fields are: AcademicYear, RecID, CredentialType, AuthorizationType, FileCreated.
  • Primary Key of StaffCredentials is RecID + CredentialType + AuthorizationType.
CourseEnrollment Table
  • This table stores one record for every school, class, class section, grade, and gender.
  • The fields are: AcademicYear, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, ClassID, CourseCode, ClassCourseID,
    GradeLevelCode, GenderCode, EnrollNoEthRpt, EnrollAmInd, EnrollAsian, EnrollPacIsl, EnrollFilipino, EnrollHispanic, EnrollAfrAm, EnrollWhite, EnrollTwoOrMore, EnrollTotal, EnrollEL, FileCreated.
  • Primary Key of CourseEnrollment is DistrictCode + SchoolCode + ClassID + CourseCode + ClassCourseID + GradeLevelCode + GenderCode.
CoursesTaught Table
  • This table stores one record for every school, ClassID, CourseCode, and ClassCourseID.
  • The fields are: AcademicYear, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, ClassID, CourseCode, ClassCourseID,
    UC CSU Approved, NCLB CORE, NCLB HQT, DistanceLearning, IndependentStudy, MultipleTeacherCode, CTE FundingProvider, Enrollment, SEID Indicator, FileCreated.
  • Primary Key of CoursesTaught is DistrictCode + SchoolCode + ClassID + CourseCode + ClassCourseID.
ClassEnrollment Table
  • This table stores one record for every school, class, class section, class grade, and gender (total enrollment for a class).
  • The fields are: AcademicYear, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, ClassID, CourseCodeOrMultCourseInd,
    AllClassCourseCodes, GradeLevelCode, GenderCode, EnrollNoEthRpt, EnrollAmInd, EnrollAsian, EnrollPacIsl, EnrollFilipino, EnrollHispanic,
    EnrollAfrAm, EnrollWhite, EnrollTwoOrMore, EnrollTotal, EnrollEL, FileCreated.
  • Primary Key of ClassEnrollment is DistrictCode + SchoolCode + ClassID + GradeLevelCode + GenderCode.

Description of the Relationship between Tables

RELATIONSHIP A 

Each StaffDemographics record will be associated with one or more StaffAssignment record(s). StaffAssignment records are either: (1) teaching assignments, which will include data in the CourseCode and ClassID fields; or (2) administrative or pupil services assignments, which will have data in the AssignmentCode field.

Note: Staff working at more than one district will have a StaffDemographics record for each district – each with the same RecID and a different DistrictCode for each district.

  • Link: RecID + DistrictCode

RELATIONSHIP B 

Each StaffAssignment record will have be associated with or more CoursesTaught record(s). Usually one course is taught in one class (one CoursesTaught record for a class). When more than one course is taught in a class (i.e., a class with two sections; French 1 and French 2), there will be a CoursesTaught record for each course taught in the class.

Note: (1) For team teaching, a CoursesTaught record will have more than one associated StaffAssignment record; (2) Pupil Services and Administrative assignments StaffAssignment records will not have associated CoursesTaught records.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
RELATIONSHIP C 

Each StaffAssignment record will be associated with zero, or one or more CourseEnrollment record(s). One CourseEnrollment record will exist for each case where there is enrollment for a grade and gender combination.

Note: For team teaching, CourseEnrollment records will have more than one associated StaffAssignment record.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
RELATIONSHIP D 

Each CoursesTaught record will be associated with zero, or one or more CourseEnrollment record(s).

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode + ClassCourseID
RELATIONSHIP E 

Each StaffDemographics record will be associated with zero, or one or more StaffCredentials record(s), containing authorizations that are valid on Census day.

Note: Some staff members may not have a credential that is valid on Census Day.

  • Link: RecID
RELATIONSHIP F 

Each StaffDemographics record will be associated with one or more StaffSchoolFTE record(s). This record will contain the total FTE for each staff member by job classification and by school.

  • Link: RecID + DistrictCode
RELATIONSHIP G 

Each StaffAssignment record will be associated with zero, or one or more ClassEnrollment record(s). One ClassEnrollment record will exist for each case where there is enrollment for a grade and gender combination.

Note: For team teaching, ClassEnrollment records will have more than one associated StaffAssignment record.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
RELATIONSHIP H 

Each CoursesTaught record will be associated with zero, or one ClassEnrollment record(s).

Note: In some cases, ClassEnrollment data are not submitted by school districts.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
Questions: Data Reporting Office | dro@cde.ca.gov | 916-327-0219 
Last Reviewed: Friday, February 16, 2024
Recently Posted in Accessing Educational Data
  • DataQuest Update, Issue #20 (added 17-May-2024)
    DataQuest is an online data reporting resource that provides access to a wide variety of data reports and downloadable data files.
  • File Structure: CBEDS Data About Schools/Districts (added 16-May-2024)
    File structure for California Basic Educational Data System (CBEDS) data about schools and districts for 2023.
  • File Structure: CBEDS by Race/Ethnic & Gender 2023 (added 16-May-2024)
    File structure for CBEDS data by race/ethnicity, gender, and school for 2023-24. Data include classified staff and home-to-school transportation data.
  • File Structure: Census Day Enrollment Data (added 16-May-2024)
    File structure for downloadable files containing Census Day TK/K–12 enrollment data from the California Longitudinal Pupil Achievement Data System (CALPADS) disaggregated by grade, race/ethnicity, gender, and other student details.
  • Two-Month Average Data Reports (added 09-Apr-2024)
    The page displays two-month average Early Education Division (EED) enrollment data for children by Program Length and Statutory Age for October/April in 2020−23.

  • Single-Month Reports (added 09-Apr-2024)
    The page displays California State Preschool Program (CSPP) Enrollment by Program Length, Statutory Age, Agency Classification, and Race/Ethnicity.
  • CSPP Enrollment by Provider County Reports (added 09-Apr-2024)
    The page provides links to annual October California State Preschool Program (CSPP) enrollment reports by provider county for 2020−22 in XLSX format.
  • Annual Aggregate Reports (added 09-Apr-2024)
    The page displays the annual California State Preschool Program enrollment data by Statutory Age and Program Length for fiscal years 2020−23, providing an unduplicated count of children served by the California Department of Education (CDE).