Workforce And Reviews ER Diagram#

This view covers staff structure, attendance, shift assignments, leave requests, and review records.

Included Tables#

`campus`
`room`
`staff`
`staffAttendance`
`staffShiftAssignment`
`staffTimeOffRequest`
`reviewType`
`review`
`reviewAnswer`

%%{init: {"securityLevel":"loose"}}%% classDiagram direction LR class campus { campusID : Number name : Text accountNumber : Text } class room { roomID : Number name : Text attendanceDateGlobal : Date } class staff { staffID : Text campusID : Number primaryRoomID : Number description : Unknown firstName : Text lastName : Text } class staffAttendance { staffAttendanceID : Text staffID : Text roomID : Number } class staffShiftAssignment { staffShiftAssignmentID : Text staffID : Text roomID : Number description : Text firstName : Unknown } class staffTimeOffRequest { staffTimeOffRequestID : Text staffID : Text approved : Number } class reviewType { reviewTypeID : Text reviewType : Text modifyTS : TimeStamp } class review { reviewID : Number staffID : Text reviewTypeID : Text roomID : Number } class reviewAnswer { reviewAnswerID : Text reviewID : Number answer : Number } staff --> campus : campusID = campusID staff --> room : primaryRoomID = roomID staffAttendance --> staff : staffID = staffID staffAttendance --> room : roomID = roomID staffShiftAssignment --> staff : staffID = staffID staffShiftAssignment --> room : roomID = roomID staffTimeOffRequest --> staff : staffID = staffID review --> staff : staffID = staffID review --> reviewType : reviewTypeID = reviewTypeID review --> room : roomID = roomID reviewAnswer --> review : reviewID = reviewID

Tables#

campus#

Primary table details live in campus under Organization And Capacity ER Diagram.

room#

Primary table details live in room under Organization And Capacity ER Diagram.

staff#

Fields#

Field	Data Type	Field Type
constantVSC	Text	Calculated
constantHealth	Text	Calculated
constantInitial	Text	Calculated
constantOne	Number	Calculated
constantOneCount	Number	Summary
constantPolicy	Text	Calculated
constantRECE	Text	Calculated
constantRecord	Text	Calculated
constantReview	Text	Calculated
constantTraining	Text	Calculated
constantYes	Text	Calculated
crossJoin	Number	Normal
date01Global	Date	Normal
date01GlobalMonth	Number	Calculated
date02Global	Date	Normal
date03Global	Date	Normal
date03GlobalMonth	Number	Calculated
date04Global	Date	Normal
date05Global	Date	Normal
date06Global	Date	Normal
number01Global	Number	Normal
ableDetail	Text	Normal
ablePhysically	Number	Normal
activeStaff	Text	Calculated
campusID	Number	Normal
staffNameFirstMiddleLast	Text	Calculated
medicationAndStorageLocation	Text	Normal
address	Text	Normal
allergiesMedicalConditions	Text	Normal
allQualsOK	Number	Normal
cclsCertificateDate	Date	Normal
cellPhone	Text	Normal
city	Text	Normal
comments	Text	Normal
vscConvictions	Text	Normal
vscConvictionsExplanation	Text	Normal
vscDocumentType	Text	Normal
vscExpiry	Date	Calculated
vscCompletionDate	Date	Normal
vscVerifiedDate	Date	Normal
vscReceiptReceived	Date	Normal
firstDayOfWork	Date	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
declarationConviction	Text	Normal
declarationConvictionExplanation	Text	Normal
declarationFormDate	Date	Normal
declarationFormExpiry	Date	Calculated
criminalCheckReason	Text	Normal
dob	Date	Normal
email	Text	Normal
emergencyContactName	Text	Normal
emergencyContactNumber	Text	Normal
emergencySupportPosition	Text	Normal
firstName	Text	Normal
modifyTS	TimeStamp	Normal
gender	Text	Normal
description	Unknown	Unknown
homePhone	Text	Normal
incompleteIllnessIncidentMedCount	Number	Normal
jobTitleID	Text	Normal
lastDayOfWork	Date	Normal
lastName	Text	Normal
lastPayrollTimecardApprovedDate	Date	Normal
lastRoomID	Number	Calculated
roomID	Unknown	Unknown
lastSupvTimecardApprovedDate	Date	Normal
lastTimestampIn	TimeStamp	Calculated
timestampIn	Unknown	Unknown
lastTimestampOut	TimeStamp	Calculated
timestampOut	Unknown	Unknown
medicalNotes	Text	Normal
mentallyCapable	Number	Normal
modifyAccount	Text	Normal
middleName	Text	Normal
onHealthSafetyCommittee	Number	Normal
payrollPIN	Binary	Normal
payTypeID	Text	Normal
policeForce	Text	Normal
PPE	Date	Normal
primaryRoomID	Number	Normal
professionalMisconduct	Text	Normal
professionalMisconductComment	Text	Normal
province	Text	Normal
reasonForTermination	Text	Normal
receExpiry	Date	Normal
receRegistration	Text	Normal
SIN	Text	Normal
healthActionsToTake	Text	Normal
staffID	Text	Normal
uploadedToCCLS	Date	Normal
zipCode	Text	Normal
text01Global	Text	Normal
text02Global	Text	Normal
rovingStaff	Number	Normal
receVerificationScreenShotModifyTS	TimeStamp	Normal
dateList01Global	Text	Normal
timecardOnly	Number	Normal
jobDeveloperNotes	Text	Normal
createdOutsideProcess	Number	Normal
createdOutsideProcessEmailSent	Number	Normal
baseHourlyRate	Number	Normal
baseSalary	Number	Normal
provincialHourlyEnhancement	Number	Normal
provincialHourlyGrant	Number	Normal
provincialSalaryEnhancement	Number	Normal
provincialSalaryGrant	Number	Normal
activeNonRovingNonTimecardOnlyStaff	Text	Calculated
typeOfDeparture	Text	Normal
receVerifiedExpiry	Date	Calculated
roeReferenceNumber	Text	Normal
primaryOrLastRoomID	Number	Calculated
date07Global	Date	Normal
retainHoursOverRatio	Number	Normal
vscReceiptExpiry	Date	Calculated
genderID	Text	Normal
jobCandidateID	Text	Normal
SINObscured	Text	Calculated
payrollPINBase64	Text	Normal
vscLastCheckAccountName	Text	Normal
vscLastCheckDate	Date	Normal
staffTransferID	Text	Normal
constantZero	Number	Calculated
staffTypeOfDepartureID	Text	Normal
staffPortalPermissionLevelID	Text	Normal
staffPortalPermissions	Text	Normal
workEmail	Text	Normal
workPhone	Text	Normal
workPhoneDisplay	Text	Calculated
phone	Unknown	Unknown
phoneExtension	Unknown	Unknown
calculateStatutoryHours	Number	Normal
cellPhoneNumeric	Text	Calculated
homePhoneNumeric	Text	Calculated
workPhoneNumeric	Text	Calculated

Relationships#

References campus on staff.campusID = campus.campusID.
References room on staff.primaryRoomID = room.roomID.
Referenced by staffAttendance on staffAttendance.staffID = staff.staffID.
Referenced by staffShiftAssignment on staffShiftAssignment.staffID = staff.staffID.
Referenced by staffTimeOffRequest on staffTimeOffRequest.staffID = staff.staffID.
Referenced by review on review.staffID = staff.staffID.

staffAttendance#

Fields#

Field	Data Type	Field Type
staffID	Text	Normal
roomID	Number	Normal
timestampIn	TimeStamp	Normal
timestampOut	TimeStamp	Normal
notes	Text	Normal
dateIn	Date	Calculated
error	Number	Calculated
timeIn	Time	Calculated
timeOut	Time	Calculated
constantOne	Number	Calculated
timestampOutMax	TimeStamp	Calculated
staffAttendanceID	Text	Normal
constantOneCount	Number	Summary
campusID	Number	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
modifyAccount	Text	Normal
modifyTS	TimeStamp	Normal
systemNote	Text	Normal
SIN	Text	Normal
dateInUnix	Text	Calculated

Relationships#

References staff on staffAttendance.staffID = staff.staffID.
References room on staffAttendance.roomID = room.roomID.

staffShiftAssignment#

Fields#

Field	Data Type	Field Type
staffShiftAssignmentID	Text	Normal
staffShiftInventoryID	Text	Normal
staffID	Text	Normal
shiftDate	Date	Normal
description	Text	Normal
firstName	Unknown	Unknown
shortDescription	Unknown	Unknown
roomID	Number	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
modifyAccount	Text	Normal
modifyTS	TimeStamp	Normal
constantOne	Number	Calculated
sort	Number	Normal
shiftDateUnix	Text	Calculated
crossJoin	Number	Normal

Relationships#

References staff on staffShiftAssignment.staffID = staff.staffID.
References room on staffShiftAssignment.roomID = room.roomID.

staffTimeOffRequest#

Fields#

Field	Data Type	Field Type
approved	Number	Normal
fromDate	Date	Normal
insufficentNotice	Text	Normal
requestDate	Date	Normal
staffID	Text	Normal
toDate	Date	Normal
staffTimeOffRequestID	Text	Normal
fromYear	Number	Calculated
toYear	Number	Calculated
eMailSentToPayroll	Text	Normal
supervisorID	Number	Normal
approvedDate	Date	Normal
campusID	Number	Normal
requestReason	Text	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
modifyAccount	Text	Normal
modifyTS	TimeStamp	Normal
staffTimeOffTypeID	Text	Normal
primaryRoomID	Number	Normal
isLocked	Number	Normal
timeBankUpdated	Number	Normal
noncompliance	Text	Normal
relatedStaffTimeOffTypeID	Text	Normal
crossJoin	Number	Normal
approvalAccount	Text	Normal
staffSignatureName	Text	Normal
staffSignatureMethod	Text	Normal
supervisorSignatureMethod	Text	Normal
supervisorSignatureName	Text	Normal
incompleteRequest	Number	Normal
newRequestEmailSent	Number	Normal

Relationships#

References staff on staffTimeOffRequest.staffID = staff.staffID.

reviewType#

Fields#

Field	Data Type	Field Type
reviewTypeID	Text	Normal
reviewType	Text	Normal
modifyTS	TimeStamp	Normal
appliesTo	Text	Normal
omitScore	Number	Normal
omitRaiseTable	Number	Normal
cbm	Number	Normal
adminOnly	Number	Normal
oneTime	Number	Normal
requiredDaysAfterFirstDay	Number	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
modifyAccount	Text	Normal
vscMedicalScreenStatement	Text	Normal
policyFailureStatement	Text	Normal
policyReadAndUnderstoodStatement	Text	Normal
wageIncreaseStatement	Text	Normal
affectsPayroll	Number	Normal
minPayrollHours	Number	Normal
reviewTypeCSS	Text	Calculated
wageIncreaseStatementCSS	Text	Calculated
policyReadAndUnderstoodStatementCSS	Text	Calculated
policyFailureStatementCSS	Text	Calculated
vscMedicalScreenStatementCSS	Text	Calculated

Relationships#

Referenced by review on review.reviewTypeID = reviewType.reviewTypeID.

review#

Fields#

Field	Data Type	Field Type
complete	Text	Normal
endDate	Date	Normal
score	Number	Calculated
answerSum	Unknown	Unknown
answerCount	Unknown	Unknown
startDate	Date	Normal
comments	Text	Normal
finalReviewDate	Date	Normal
reviewID	Number	Normal
staffID	Text	Normal
constantOne	Number	Calculated
reviewTypeID	Text	Normal
vscOption	Text	Normal
vscExplanation	Text	Normal
currentMonth	Number	Calculated
roomID	Number	Normal
crossJoin	Number	Normal
meetingDate	Date	Normal
meetingTime	Time	Normal
cbm	Number	Normal
text01Global	Text	Normal
isLocked	Number	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
modifyAccount	Text	Normal
modifyTS	TimeStamp	Normal
reviewType	Text	Normal
vscMedicalScreenStatement	Text	Normal
policyFailureStatement	Text	Normal
policyReadAndUnderstoodStatement	Text	Normal
wageIncreaseStatement	Text	Normal
staffSignatureMethod	Text	Normal
staffSignatureName	Text	Normal
supervisorSignatureMethod	Text	Normal
supervisorSignatureName	Text	Normal

Relationships#

References staff on review.staffID = staff.staffID.
References reviewType on review.reviewTypeID = reviewType.reviewTypeID.
References room on review.roomID = room.roomID.
Referenced by reviewAnswer on reviewAnswer.reviewID = review.reviewID.

reviewAnswer#

Fields#

Field	Data Type	Field Type
answer	Number	Normal
answerCount	Number	Summary
answerSum	Number	Summary
comment	Text	Normal
constantOne	Number	Calculated
policyList	Text	Calculated
policyName	Unknown	Unknown
reviewAnswerID	Text	Normal
reviewID	Number	Normal
reviewQuestionID	Number	Normal
sort	Number	Normal
timestampAssessed	TimeStamp	Normal
followUpComment	Text	Normal
createAccount	Text	Normal
createTS	TimeStamp	Normal
modifyAccount	Text	Normal
modifyTS	TimeStamp	Normal
isLocked	Number	Normal
reviewTypeID	Text	Normal
reviewQuestionAreaID	Text	Normal
question	Text	Normal
score1Meaning	Text	Normal
score2Meaning	Text	Normal
score3Meaning	Text	Normal
score4Meaning	Text	Normal
reviewQuestionArea	Text	Normal

Relationships#

References review on reviewAnswer.reviewID = review.reviewID.

Notes#

The staff graph also includes compliance, document, qualification-history, and whiteboard-oriented tables. Those are omitted here to keep the workforce operational core visible.
Leave and shift overlap logic in the legacy export is implemented through many date-filtered occurrences rather than a single normalized conflict table.