Table: dbo.Employee
Columns
Name | Type | Constraints | Description
employee_KEY | int (4) | NOT NULL | Primary key for this table
client_KEY | int (4) | NOT NULL | Key of company employee works for
employee_id | nvarchar (22) | NOT NULL | Clients employee identifier
social_security_number | nvarchar (22) | NOT NULL | Social Security Number
first_name | nvarchar (60) | NOT NULL | First name
middle_name | nvarchar (60) | NOT NULL | Middle name
last_name | nvarchar (60) | NOT NULL | Last name
name_suffix | nvarchar (10) | NOT NULL | Name suffix (Jr, Sr, MD, etc.)
contact_KEY | int (4) | NOT NULL | Key of parent contact record.
date_of_birth | smalldatetime (4) |  | Date of birth
marital_status_type_KEY | int (4) |  | Marital status type
gender_type_KEY | int (4) |  | Gender type
race_type_KEY | int (4) |  | Race type
is_officer | bit (1) | NOT NULL | Indicates if the employee is an officer of the company
is_probationary | bit (1) | NOT NULL | Indicates if this employee is probationary
is_seasonal | bit (1) | NOT NULL | Indicates if this employee is seasonal
is_family_of_owner | bit (1) | NOT NULL | Indicates if this employee is a member of the family that owns the company
w2_statutory_employee | bit (1) | NOT NULL | Indicates if this is a W2 statutory employee
ste_location_code | nvarchar (70) | NOT NULL | Symmetry Tax Engine generated code which links the employee taxing address to its related jurisdiction-related tax items
primary__client_pay_schedule_KEY | int (4) |  | Primary pay schedule
alternate__client_pay_schedule_KEY | int (4) |  | Alternate payschedule
is_paycard_recipient | bit (1) | NOT NULL | Indicates if this employee is paid via deposits to a bankcard account
direct_deposit_method_KEY | nvarchar (50) | NOT NULL | Method used for Direct Deposit
direct_deposit_amount_percent | decimal (5, 2) | NOT NULL | Percentage of pay to be distrubted using direct deposit
direct_deposit_distribution_method_KEY | int (4) | NOT NULL | Indicates if this employee direct deposit allocation method is Total Distribution (1) or Remainder of Distribution (2)
use_alternate_direct_deposit_distribution | bit (1) | NOT NULL | Enables the use of an alternate set of direct deposit allocation distributions
distribute_pay_by_percentage | bit (1) | NOT NULL | Enables an employee so that pay can be distributed among different work locations/departments
employee_id_sortable | nvarchar (96) | NOT NULL | A representation of the employee_id column that sorts in natural order. A trigger keeps this field synchronized with the content of employee_id.
date_ssn_verified | smalldatetime (4) |  | The date the ssn for this employee was verified.
date_new_hire_reported | smalldatetime (4) |  | The date this employee was included in a new hire report.
record_status_KEY | int (4) | NOT NULL | The current status for the employee record.
employee_type_KEY | int (4) | NOT NULL | The current type of the employee record.
use_backup_withholding_rate | bit (1) | NOT NULL | If true, then the employee uses the appropriate rate from the Backup_Withholding_Rate table.
current__employee_earning_type_KEY | int (4) | NOT NULL | Foreign key to the employee earning type table.
is_webemployee_portal_disabled | bit (1) | NOT NULL | Flag to disable the employee WebEmployee portal.
is_webemployee_portal_activated | bit (1) | NOT NULL | Flag to indicate the employee WebEmployee portal has been activated. The portal is activated when the user logs in for the first time.
resend_employee_self_service_welcome_email | bit (1) | NOT NULL | Flag to indicate if ESS needs to re-send a welcome email to the employee email address.
transmit_is_webemployee_portal_disabled | bit (1) | NOT NULL | Flag to indicate that we need to push the portal disabled flag to ESS.
has_health_care | bit (1) | NOT NULL | Ture if this employee has health care which is provided by employer or from somewhere else.
export__quick_books_payee_KEY | int (4) |  | A Foreign key to the Quick_Books_Payee table, this is the quickbooks side of the mapping during the Acs to Quickbooks export data mapping process. This will be used to map Accounting CS employees to QuickBooks payees of type employee. This key will be null if no export mapping has been done.
employee_multi_state_income_tax_calculation_method_KEY | int (4) | NOT NULL | The selected method for determining how to calculate multi-state income tax withholding for the employee.
is_w2_retirement_plan | bit (1) | NOT NULL | Is this employee marked as having a retirement plan.
is_part_time | bit (1) | NOT NULL | Indicates that the employee has part time employment status.
affordable_care_act_employee_status_KEY | tinyint (1) | NOT NULL |
employee_version | timestamp (8) | NOT NULL | The version of this row. It's an aid in versioning the row.
employee_force_new_version_toggle | bit (1) | NOT NULL | A bit to toggle that causes the employee_version to update. Flip this bit when a subordinate tables are changed.
forced_webemployee_push_version | tinyint (1) | NOT NULL | Allows for us to create employee update pushes to web employee based on a version. Starts at zero and is incremented for each push we run. See DevWiki documentation for details on ini toggle ForcedEmployeeEssPushVersion.
covid_19_defer_employee_social_security | bit (1) | NOT NULL | Bit field that indicates whether or not the Employee Social Security tax will be deferred. False means that the tax will not be deferred, true means that the tax will be deferred.
covid_19_exempt_employee_from_employee_retention_credit | bit (1) | NOT NULL | Bit field that indicates whether or not the Employee Rentention Credit will be excluded. True means that the credit will be excluded. False means that the credit will be included.
first__treasury_tipped_occupation_code_KEY | tinyint (1) |  | Foreign key to Treasury_Tipped_Occupation_Code. Specifies the first W-2 Treasury tipped occupation code for this employee. NULL indicates no tipped occupation code assigned.
second__treasury_tipped_occupation_code_KEY | tinyint (1) |  | Foreign key to Treasury_Tipped_Occupation_Code. Specifies the second W-2 Treasury tipped occupation code for this employee. NULL indicates no tipped occupation code assigned.

Primary key
Name | Columns | Description
PK_Employee | employee_KEY |
Unique constraints
Name | Columns | Description
UK_Employee$client_KEY$employee_id | client_KEY, employee_id |
UK_Employee$client_KEY$employee_id_sortable | client_KEY, employee_id_sortable |
UK_Employee$contact_KEY | contact_KEY |

Foreign keys
Name | Columns | Referenced table | Description
FK_Employee__Treasury_Tipped_Occupation_Code__first__treasury_tipped_occupation_code_KEY | first__treasury_tipped_occupation_code_KEY | Treasury_Tipped_Occupation_Code (treasury_tipped_occupation_code_KEY) | Foreign key constraint linking Employee to Treasury_Tipped_Occupation_Code for the first W-2 tipped occupation code.
FK_Employee__Treasury_Tipped_Occupation_Code__second__treasury_tipped_occupation_code_KEY | second__treasury_tipped_occupation_code_KEY | Treasury_Tipped_Occupation_Code (treasury_tipped_occupation_code_KEY) | Foreign key constraint linking Employee to Treasury_Tipped_Occupation_Code for the second W-2 tipped occupation code.
FK_Employee__Client__client_KEY | client_KEY | Client (client_KEY) |
FK_Employee__Client_Pay_Schedule__alternate__client_pay_schedule_KEY | alternate__client_pay_schedule_KEY | Client_Pay_Schedule (client_pay_schedule_KEY) |
FK_Employee__Client_Pay_Schedule__primary__client_pay_schedule_KEY | primary__client_pay_schedule_KEY | Client_Pay_Schedule (client_pay_schedule_KEY) |
FK_Employee__Contact__contact_KEY | contact_KEY | Contact (contact_KEY) |
FK_Employee__Affordable_Care_Act_Employee_Status__affordable_care_act_employee_status_KEY | affordable_care_act_employee_status_KEY | Affordable_Care_Act_Employee_Status (affordable_care_act_employee_status_KEY) |
FK_Employee__Direct_Deposit_Distribution_Method__direct_deposit_distribution_method_KEY | direct_deposit_distribution_method_KEY | Direct_Deposit_Distribution_Method (direct_deposit_distribution_method_KEY) |
FK_Employee__Direct_Deposit_Method__direct_deposit_method_KEY | direct_deposit_method_KEY | Direct_Deposit_Method (direct_deposit_method_KEY) |
FK_Employee__Employee_Earning_Type__current__employee_earning_type_KEY | current__employee_earning_type_KEY | Employee_Earning_Type (employee_earning_type_KEY) |
FK_Employee__Employee_Multi_State_Income_Tax_Calculation_Method__employee_multi_state_income_tax_calculation_method_KEY | employee_multi_state_income_tax_calculation_method_KEY | Employee_Multi_State_Income_Tax_Calculation_Method (employee_multi_state_income_tax_calculation_method_KEY) |
FK_Employee__Employee_Type__employee_type_KEY | employee_type_KEY | Employee_Type (employee_type_KEY) |
FK_Employee__Gender_Type__gender_type_KEY | gender_type_KEY | Gender_Type (gender_type_KEY) |
FK_Employee__Marital_Status_Type__marital_status_type_KEY | marital_status_type_KEY | Marital_Status_Type (marital_status_type_KEY) |
FK_Employee__Quick_Books_Payee__export__quick_books_payee_KEY | export__quick_books_payee_KEY | Quick_Books_Payee (quick_books_payee_KEY) |
FK_Employee__Race_Type__race_type_KEY | race_type_KEY | Race_Type (race_type_KEY) |
FK_Employee__Record_Status__record_status_KEY | record_status_KEY | Record_Status (record_status_KEY) |
FK_Employee__Ste_Location_Code__ste_location_code | ste_location_code | Ste_Location_Code (ste_location_code) |

Incoming foreign keys
Name | Columns | Referencing table | Description
FK_Employee_Self_Service_W2_Queue__Employee__employee_KEY | employee_KEY | Employee_Self_Service_W2_Queue |
FK_Employee_W2_Rendered_Datashare__Employee__employee_KEY | employee_KEY | Employee_W2_Rendered_Datashare |
FK_Custom_Value_Employee__Employee__employee_KEY | employee_KEY | Custom_Value_Employee |
FK_Employee_Benefit__Employee__employee_KEY | employee_KEY | Employee_Benefit |
FK_Employee_Reconciliation_Tax_Form_Tax_Jurisdiction_Override__Employee__employee_KEY | employee_KEY | Employee_Reconciliation_Tax_Form_Tax_Jurisdiction_Override |
FK_Garnishment_Order__Employee__employee_KEY | employee_KEY | Garnishment_Order |
FK_Form_1099__Employee__employee_KEY | employee_KEY | Form_1099 |
FK_Employee_1095_Override__Employee__employee_KEY | employee_KEY | Employee_1095_Override |
FK_Shortfall_Transaction__Employee__employee_KEY | employee_KEY | Shortfall_Transaction |
FK_Timerack_Employee_Update_Queue__Employee__employee_KEY | employee_KEY | Timerack_Employee_Update_Queue |
FK_Employee_W2_Verification_Dates__Employee__employee_KEY | employee_KEY | Employee_W2_Verification_Dates |
FK_Print_W2_Selection__Employee__employee_KEY | employee_KEY | Print_W2_Selection |
FK_XactPay_Employee_SSN_History__Employee__employee_KEY | employee_KEY | XactPay_Employee_SSN_History |
FK_Quick_Books_Payee_N_Employee__Employee__employee_KEY | employee_KEY | Quick_Books_Payee_N_Employee |
FK_Payroll_Bank_Transaction__Employee__employee_KEY | employee_KEY | Payroll_Bank_Transaction |
FK_Ascii_Time_Clock_Employee_Id_Map__Employee__employee_KEY | employee_KEY | Ascii_Time_Clock_Employee_Id_Map |
FK_Employee_Taxable_Wage_Adjustment__Employee__employee_KEY | employee_KEY | Employee_Taxable_Wage_Adjustment |
FK_Employee_W2_Filing__Employee__employee_KEY | employee_KEY | Employee_W2_Filing | FOREIGN KEY constraint of employee_KEY for the Employee_W2_Filing table which ensures we have a valid employee
FK_Employee_Other_State_Setting__Employee__employee_KEY | employee_KEY | Employee_Other_State_Setting |
FK_Employee_W2_Box_12_Other_Item__Employee__employee_KEY | employee_KEY | Employee_W2_Box_12_Other_Item |
FK_Employee_Self_Service_1095_Queue__Employee__employee_KEY | employee_KEY | Employee_Self_Service_1095_Queue |
FK_Workforce_Hub_Employee_Update_Queue__Employee__employee_KEY | employee_KEY | Workforce_Hub_Employee_Update_Queue |
FK_Employee_W2_Box_14_Other_Item__Employee__employee_KEY | employee_KEY | Employee_W2_Box_14_Other_Item |
FK_Workforce_Hub_Form_Queue__Employee__employee_KEY | employee_KEY | Workforce_Hub_Form_Queue |
FK_Employee_Work_Location_Payroll_Department__Employee__employee_KEY | employee_KEY | Employee_Work_Location_Payroll_Department |
FK_Employee_Political_Subdivision_Code_Override__Employee__employee_KEY | employee_KEY | Employee_Political_Subdivision_Code_Override |
FK_Print_1095_Selection__Employee__employee_KEY | employee_KEY | Print_1095_Selection |
FK_Employee_Independent_Contractor_Reported__Employee__employee_KEY | employee_KEY | Employee_Independent_Contractor_Reported |
FK_Employee_W2_Rendered_Document__Employee__employee_KEY | employee_KEY | Employee_W2_Rendered_Document |
FK_Employee_Tax_Form_Locality_Amount_Override__Employee__employee_KEY | employee_KEY | Employee_Tax_Form_Locality_Amount_Override |
FK_Recorded_Event_Object_N_Employee__Employee__employee_KEY | employee_KEY | Recorded_Event_Object_N_Employee |
FK_Employee_W4_Setting__Employee__employee_KEY | employee_KEY | Employee_W4_Setting |
FK_Employee_Payroll_Item__Employee__employee_KEY | employee_KEY | Employee_Payroll_Item |
FK_Employee_Event__Employee__employee_KEY | employee_KEY | Employee_Event |
FK_Employee_Direct_Deposit_Allocation__Employee__employee_KEY | employee_KEY | Employee_Direct_Deposit_Allocation |
FK_Employee_W2_Rendering_Queue__Employee__employee_KEY | employee_KEY | Employee_W2_Rendering_Queue |
FK_Pod_Service_W2_Queue__Employee__empoyee_KEY | employee_KEY | Pod_Service_W2_Queue |
FK_Employee_1095__Employee__employee_KEY | employee_KEY | Employee_1095 |
FK_Employee_Worker_Compensation_Item__Employee__employee_KEY | employee_KEY | Employee_Worker_Compensation_Item |
FK_Employee_Tax_Item__Employee__employee_KEY | employee_KEY | Employee_Tax_Item |
FK_Employee_Self_Service_1099_Queue__Employee__employee_KEY | employee_KEY | Employee_Self_Service_1099_Queue |
FK_Web_Employee_Employee_Update_Queue__Employee__employee_KEY | employee_KEY | Web_Employee_Employee_Update_Queue |
FK_Employee_Tip_Allocation__Employee__employee_KEY | employee_KEY | Employee_Tip_Allocation |
FK_Employee_Tax_Jurisdiction__Employee__employee_KEY | employee_KEY | Employee_Tax_Jurisdiction |
FK_Employee_W2_In_Process_Rendering_Queue__Employee__employee_KEY | employee_KEY | Employee_W2_In_Process_Rendering_Queue |
FK_Payroll_Check_Payroll_Item_Import__Employee__employee_KEY | employee_KEY | Payroll_Check_Payroll_Item_Import |
FK_Payroll_Check_Tax_Item_Import__Employee__employee_KEY | employee_KEY | Payroll_Check_Tax_Item_Import |