Staff — Data & read/write tables
Database view for Staff + external concepts Staff references. Convention: 📖 READ, ✏️ WRITE (insert/update).
Related external concepts
| Concept | What it is | Role in Staff | Owning domain |
|---|---|---|---|
| EnterpriseUser | Cross-BU person identity (enterpriseUserId). | Used to dedupe one-person-one-row in Weekly Planner (merge Employee records for same person). | Identity/Enterprise |
| BusinessUnit / Org (Site) | Unit / site. | Data scope; cross-BU merge keeps selected BU's employeeId as canonical. | Org |
TrackingCode (via ShiftTrackingCode) | Classification code; TypeId = Leave / Shift. | Leave forms only show TypeId = Leave; attached to leave/shift. | Master Data |
Attendee (via Attendee_Employee) | Child/student linked to staff. | Some staff–attendee features (outside leave core). | Attendee |
| TermProduct / Session | Class/session event. | Displayed on Timetable (Syncfusion ScheduleComponent). | Program/Term |
| Permission / PermissionTemplate | Permissions. | Filter staff by role in planner. | Identity/Access |
Main data tables
| Group | Table | Purpose |
|---|---|---|
| Staff | Employee | Staff record in a BU (RoleId, enterprise link). |
Org_Employee | Staff ↔ site/org link. | |
Employee_PermissionTemplate | Permission assignment. | |
| Roster | Shift | Work shift (time interval). |
ShiftRecurrence | Recurring shift. | |
ShiftTrackingCode | Tracking code attached to shifts (and source of Leave/Shift codes). | |
ShiftClock | Clock in/out. | |
| Leave | EmployeeAbsence | Leave/absence: date-time interval, status, tracking code. |
| Availability | EmployeeAvailability, EmployeeAvailabilityLine | Availability by weekday + time slot. |
| Compliance | EmployeeCompliance | Documents/certificates (source for expiry reminder). |
⚠️ Leave is stored in
EmployeeAbsence(absence). Many Staff rules are display-only (sort/dedupe/layout) — these tables do not change.
Tables in each flow
Flow 1 — Roster Weekly Planner (admin schedules shifts)
| Action | Tables | Purpose |
|---|---|---|
| Load staff rows | Employee, Org_Employee 📖 READ | Fetch staff by role + site; dedupe by enterpriseUserId. |
| Show leave overlap | EmployeeAbsence 📖 READ | Compute interval overlap with shift interval → read-only Leave field. |
| Create/edit shift | Shift, ShiftRecurrence, ShiftTrackingCode ✏️ WRITE | Save shift + tracking code. |
Flow 2 — Leave (request / approve)
| Action | Tables | Purpose |
|---|---|---|
| Load tracking codes | ShiftTrackingCode 📖 READ | Filter TypeId = Leave. |
| Create/edit leave | EmployeeAbsence ✏️ WRITE | Save leave + selected tracking code. |
| Approve leave | EmployeeAbsence ✏️ UPDATE | Advance approval status; attach tracking code. |
Flow 3 — Availability & Timetable
| Action | Tables | Purpose |
|---|---|---|
| View/edit availability | EmployeeAvailability, EmployeeAvailabilityLine 📖✏️ | Time slots by weekday (UI sorts by start time). |
| Display timetable | Shift 📖 READ; TermProduct/session 📖 READ | Event grid by day/week. |
Related
- Concepts & architecture — leave, roster, tracking code.
- Common cases — leave overlap, cross-BU, slot sorting.