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 |