Table: Staff
Columns
NameTypeConstraintsDescription
staff_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table.
update__staff_KEYintNOT NULL
update_date_utcdatetimeNOT NULL
staff_idnvarchar (11)NOT NULLUser defined identifier (unique).
descriptionnvarchar (250)NOT NULLUser defined description.
user_namenvarchar (30)NOT NULLThe name used on the login screen.
first_namenvarchar (30)NOT NULLThe first name of this Staff.
middle_namenvarchar (30)NOT NULLThe middle name of this Staff.
last_namenvarchar (30)NOT NULLThe last name of this Staff.
social_security_numbernvarchar (11)NOT NULLSocial Security Number.
photographvarbinary (-1)The contents of a image file for the staff's photograph.
commentsnvarchar (-1)NOT NULLAny comments about the Staff.
date_hiredsmalldatetimeDate hired.
date_leftsmalldatetimeDate Staff no longer employed.
date_education_metsmalldatetimeDate education met.
date_exam_passedsmalldatetimeDate exam passed.
date_experience_metsmalldatetimeDate experience met.
date_certifiedsmalldatetimeDate certified.
certification_numbernvarchar (250)NOT NULLCertification number.
staff_status_KEYintNOT NULLKey from one of the already set up Staff Status.
staff_level_KEYintKey from one of the already set up Staff Levels.
office_KEYintKey from one of the already set up Offices.
department_KEYintKey from one of the already set up Departments.
supervisor__staff_KEYintKey of the staff that is this Staff's supervisor.
contact_KEYintNOT NULLKey to the primary Contact record for this Staff.
create_date_utcdatetimeNOT NULL
staff_id_sortablevarchar (48)NOT NULLA representation of the staff_id column that sorts in natural order, meaning that digits are sorted as numbers while non-digits are sorted alphabetically. A trigger keeps this field synchronized with the content of staff_id.
Primary key
NameColumnsDescription
PK_Staff$staff_KEYstaff_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Staff$contact_KEYcontact_KEYEach Staff must have a different primary Contact.
UK_Staff$staff_idstaff_idEach id must be unique.
UK_Staff$staff_id_sortablestaff_id_sortableAn index by the sortable ID is desired, and the algorithm ends up producing unique values.
Indexes
NameColumnsDescription
IX_Staff$date_left@staff_iddate_left, staff_idCovers the columns needed when getting the list of staff for logging in. Used by sec_Get_Login_Information.
IX_Staff$department_KEYdepartment_KEYIndex supports foreign key FK_Staff__Department__department_KEY
IX_Staff$office_KEYoffice_KEY
IX_Staff$staff_KEY$staff_idstaff_KEY, staff_id
IX_Staff$staff_level_KEYstaff_level_KEYIndex supports foreign key FK_Staff__Staff_Level__staff_level_KEY
IX_Staff$staff_status_KEYstaff_status_KEYIndex supports foreign key FK_Staff__Staff_Status__staff_status_KEY
IX_Staff$supervisor__staff_KEYsupervisor__staff_KEYIndex supports foreign key FK_Staff__Staff__supervisor__staff_KEY
IX_Staff$update_date_utcupdate_date_utc
IX_Staff$user_name$staff_KEYuser_name, staff_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Staff__Contact__contact_KEYcontact_KEYContact
FK_Staff__Department__department_KEYdepartment_KEYDepartment
FK_Staff__Office__office_KEYoffice_KEYOffice
FK_Staff__Staff_Level__staff_level_KEYstaff_level_KEYStaff_Level
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Active_Filter__Staff__staff_KEYstaff_KEYActive_Filteron delete cascade;
FK_Client__Staff__associate__staff_KEYassociate__staff_KEYClient
FK_Client__Staff__manager__staff_KEYmanager__staff_KEYClient
FK_Client__Staff__referred_by__staff_KEYreferred_by__staff_KEYClient
FK_Custom_Portlet__Staff__private__staff_KEYprivate__staff_KEYCustom_Portleton delete cascade;
FK_Feed_Channel_Item_Staff_Read__Staff__staff_KEYstaff_KEYFeed_Channel_Item_Staff_Readon delete cascade;
FK_Login_Ticket__Staff__staff_KEYstaff_KEYLogin_Ticketon delete cascade;
FK_Outlook_Contact_Synchronization_Profile__Staff__staff_KEYstaff_KEYOutlook_Contact_Synchronization_Profileon delete cascade;
FK_Practice_User__Staff__staff_KEYstaff_KEYPractice_Useron delete cascade;
FK_Project_Template__Staff__responsible__staff_KEYresponsible__staff_KEYProject_Templateon delete set null;
FK_Report_Profile__Staff__staff_KEYstaff_KEYReport_Profileon delete cascade;
FK_Restricted_Clearance_Staff__Staff__staff_KEYstaff_KEYRestricted_Clearance_Staffon delete cascade;
FK_Staff_Area_Rating__Staff__staff_KEYstaff_KEYStaff_Area_Rating
FK_Staff_Benefit__Staff__staff_KEYstaff_KEYStaff_Benefiton delete cascade;
FK_Staff_Locator__Staff__staff_KEYstaff_KEYStaff_Locatoron delete cascade;
FK_Staff_Rate_Table__Staff__staff_KEYstaff_KEYStaff_Rate_Table
FK_Staff_Report_Definition__Staff__staff_KEYstaff_KEYStaff_Report_Definitionon delete cascade;
FK_Staff_Security_Group__Staff__staff_KEYstaff_KEYStaff_Security_Groupon delete cascade;
FK_Staff_Shortcut_Group__Staff__staff_KEYstaff_KEYStaff_Shortcut_Groupon delete cascade;
FK_Staff_Target_Range__Staff__staff_KEYstaff_KEYStaff_Target_Range
FK_User_Session__Staff__staff_KEYstaff_KEYUser_Sessionon delete cascade;
FK_Client_Staff_Grouping__Staff__staff_KEYstaff_KEYClient_Staff_Grouping
FK_Engagement__Staff__biller__staff_KEYbiller__staff_KEYEngagement
FK_Engagement__Staff__manager__staff_KEYmanager__staff_KEYEngagement
FK_Engagement__Staff__recurring_bill__staff_KEYrecurring_bill__staff_KEYEngagement
FK_Engagement__Staff__reviewer__staff_KEYreviewer__staff_KEYEngagement
FK_Ledger_Entry__Staff__approved__staff_KEYapproved__staff_KEYLedger_Entry
FK_Ledger_Entry__Staff__posted__staff_KEYposted__staff_KEYLedger_Entry
FK_Print_Option__Staff__fcs__staff_KEYfcs__staff_KEYPrint_Option
FK_Print_Option__Staff__staff_KEYstaff_KEYPrint_Optionon delete cascade;
FK_Project_Template_Assignment__Staff__staff_KEYstaff_KEYProject_Template_Assignmenton delete cascade;
FK_User_Preference__Staff__staff_KEYstaff_KEYUser_Preferenceon delete cascade;
FK_Invoice__Staff__billed__staff_KEYbilled__staff_KEYInvoice
FK_Recorded_Event_Notification__Staff__staff_KEYstaff_KEYRecorded_Event_Notificationon delete cascade;
FK_Schedule_Item__Staff__approved__staff_KEYapproved__staff_KEYSchedule_Item
FK_Task_Template_Assignment__Staff__staff_KEYstaff_KEYTask_Template_Assignmenton delete cascade;
FK_Project__Staff__responsible__staff_KEYresponsible__staff_KEYProject
FK_Schedule_Entry__Staff__staff_KEYstaff_KEYSchedule_Entry
FK_Schedule_Item_Assignment__Staff__staff_KEYstaff_KEYSchedule_Item_Assignmenton delete cascade;
FK_Sheet_Entry__Staff__approved__staff_KEYapproved__staff_KEYSheet_Entry
FK_Sheet_Entry__Staff__posted__staff_KEYposted__staff_KEYSheet_Entry
FK_Sheet_Entry__Staff__staff_KEYstaff_KEYSheet_Entry

Schema diagram