Table: Staff
Columns
Name | Type | Constraints | Description
staff_KEY | int | NOT NULL | Assigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table.
update__staff_KEY | int | NOT NULL |
update_date_utc | datetime | NOT NULL |
staff_id | nvarchar (11) | NOT NULL | User defined identifier (unique).
description | nvarchar (250) | NOT NULL | User defined description.
user_name | nvarchar (30) | NOT NULL | The name used on the login screen.
first_name | nvarchar (30) | NOT NULL | The first name of this Staff.
middle_name | nvarchar (30) | NOT NULL | The middle name of this Staff.
last_name | nvarchar (30) | NOT NULL | The last name of this Staff.
social_security_number | nvarchar (11) | NOT NULL | Social Security Number.
photograph | varbinary (-1) |  | The contents of a image file for the staff's photograph.
comments | nvarchar (-1) | NOT NULL | Any comments about the Staff.
date_hired | smalldatetime |  | Date hired.
date_left | smalldatetime |  | Date Staff no longer employed.
date_education_met | smalldatetime |  | Date education met.
date_exam_passed | smalldatetime |  | Date exam passed.
date_experience_met | smalldatetime |  | Date experience met.
date_certified | smalldatetime |  | Date certified.
certification_number | nvarchar (250) | NOT NULL | Certification number.
staff_status_KEY | int | NOT NULL | Key from one of the already set up Staff Status.
staff_level_KEY | int |  | Key from one of the already set up Staff Levels.
office_KEY | int |  | Key from one of the already set up Offices.
department_KEY | int |  | Key from one of the already set up Departments.
supervisor__staff_KEY | int |  | Key of the staff that is this Staff's supervisor.
contact_KEY | int | NOT NULL | Key to the primary Contact record for this Staff.
create_date_utc | datetime | NOT NULL |
staff_id_sortable | varchar (48) | NOT NULL | A 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
Name | Columns | Description
PK_Staff$staff_KEY | staff_KEY | CLUSTERED
Unique constraints
Name | Columns | Description
UK_Staff$contact_KEY | contact_KEY | Each Staff must have a different primary Contact.
UK_Staff$staff_id | staff_id | Each id must be unique.
UK_Staff$staff_id_sortable | staff_id_sortable | An index by the sortable ID is desired, and the algorithm ends up producing unique values.
Indexes
Name | Columns | Description
IX_Staff$date_left@staff_id | date_left, staff_id | Covers the columns needed when getting the list of staff for logging in. Used by sec_Get_Login_Information.
IX_Staff$department_KEY | department_KEY | Index supports foreign key FK_Staff__Department__department_KEY
IX_Staff$office_KEY | office_KEY |
IX_Staff$staff_KEY$staff_id | staff_KEY, staff_id |
IX_Staff$staff_level_KEY | staff_level_KEY | Index supports foreign key FK_Staff__Staff_Level__staff_level_KEY
IX_Staff$staff_status_KEY | staff_status_KEY | Index supports foreign key FK_Staff__Staff_Status__staff_status_KEY
IX_Staff$supervisor__staff_KEY | supervisor__staff_KEY | Index supports foreign key FK_Staff__Staff__supervisor__staff_KEY
IX_Staff$update_date_utc | update_date_utc |
IX_Staff$user_name$staff_KEY | user_name, staff_KEY |

Foreign keys
Name | Columns | Referenced table | Description
FK_Staff__Contact__contact_KEY | contact_KEY | Contact |
FK_Staff__Department__department_KEY | department_KEY | Department |
FK_Staff__Office__office_KEY | office_KEY | Office |
FK_Staff__Staff_Level__staff_level_KEY | staff_level_KEY | Staff_Level |

Incoming foreign keys
Name | Columns | Referencing table | Description
FK_Active_Filter__Staff__staff_KEY | staff_KEY | Active_Filter | on delete cascade;
FK_Client__Staff__associate__staff_KEY | associate__staff_KEY | Client |
FK_Client__Staff__manager__staff_KEY | manager__staff_KEY | Client |
FK_Client__Staff__referred_by__staff_KEY | referred_by__staff_KEY | Client |
FK_Custom_Portlet__Staff__private__staff_KEY | private__staff_KEY | Custom_Portlet | on delete cascade;
FK_Feed_Channel_Item_Staff_Read__Staff__staff_KEY | staff_KEY | Feed_Channel_Item_Staff_Read | on delete cascade;
FK_Outlook_Contact_Synchronization_Profile__Staff__staff_KEY | staff_KEY | Outlook_Contact_Synchronization_Profile | on delete cascade;
FK_Practice_User__Staff__staff_KEY | staff_KEY | Practice_User | on delete cascade;
FK_Project_Template__Staff__responsible__staff_KEY | responsible__staff_KEY | Project_Template | on delete set null;
FK_Report_Profile__Staff__staff_KEY | staff_KEY | Report_Profile | on delete cascade;
FK_Restricted_Clearance_Staff__Staff__staff_KEY | staff_KEY | Restricted_Clearance_Staff | on delete cascade;
FK_Staff_Area_Rating__Staff__staff_KEY | staff_KEY | Staff_Area_Rating |
FK_Staff_Benefit__Staff__staff_KEY | staff_KEY | Staff_Benefit | on delete cascade;
FK_Staff_Locator__Staff__staff_KEY | staff_KEY | Staff_Locator | on delete cascade;
FK_Staff_Rate_Table__Staff__staff_KEY | staff_KEY | Staff_Rate_Table |
FK_Staff_Report_Definition__Staff__staff_KEY | staff_KEY | Staff_Report_Definition | on delete cascade;
FK_Staff_Security_Group__Staff__staff_KEY | staff_KEY | Staff_Security_Group | on delete cascade;
FK_Staff_Shortcut_Group__Staff__staff_KEY | staff_KEY | Staff_Shortcut_Group | on delete cascade;
FK_Staff_Target_Range__Staff__staff_KEY | staff_KEY | Staff_Target_Range |
FK_User_Session__Staff__staff_KEY | staff_KEY | User_Session | on delete cascade;
FK_Client_Staff_Grouping__Staff__staff_KEY | staff_KEY | Client_Staff_Grouping |
FK_Engagement__Staff__biller__staff_KEY | biller__staff_KEY | Engagement |
FK_Engagement__Staff__manager__staff_KEY | manager__staff_KEY | Engagement |
FK_Engagement__Staff__recurring_bill__staff_KEY | recurring_bill__staff_KEY | Engagement |
FK_Engagement__Staff__reviewer__staff_KEY | reviewer__staff_KEY | Engagement |
FK_Ledger_Entry__Staff__approved__staff_KEY | approved__staff_KEY | Ledger_Entry |
FK_Ledger_Entry__Staff__posted__staff_KEY | posted__staff_KEY | Ledger_Entry |
FK_Print_Option__Staff__fcs__staff_KEY | fcs__staff_KEY | Print_Option |
FK_Print_Option__Staff__staff_KEY | staff_KEY | Print_Option | on delete cascade;
FK_Project_Template_Assignment__Staff__staff_KEY | staff_KEY | Project_Template_Assignment | on delete cascade;
FK_User_Preference__Staff__staff_KEY | staff_KEY | User_Preference | on delete cascade;
FK_Invoice__Staff__billed__staff_KEY | billed__staff_KEY | Invoice |
FK_Recorded_Event_Notification__Staff__staff_KEY | staff_KEY | Recorded_Event_Notification | on delete cascade;
FK_Schedule_Item__Staff__approved__staff_KEY | approved__staff_KEY | Schedule_Item |
FK_Task_Template_Assignment__Staff__staff_KEY | staff_KEY | Task_Template_Assignment | on delete cascade;
FK_Project__Staff__responsible__staff_KEY | responsible__staff_KEY | Project |
FK_Schedule_Entry__Staff__staff_KEY | staff_KEY | Schedule_Entry |
FK_Schedule_Item_Assignment__Staff__staff_KEY | staff_KEY | Schedule_Item_Assignment | on delete cascade;
FK_Sheet_Entry__Staff__approved__staff_KEY | approved__staff_KEY | Sheet_Entry |
FK_Sheet_Entry__Staff__posted__staff_KEY | posted__staff_KEY | Sheet_Entry |
FK_Sheet_Entry__Staff__staff_KEY | staff_KEY | Sheet_Entry |