Table: dbo.Employee
Columns
NameTypeConstraintsDescription
employee_KEYint (4)NOT NULLPrimary key for this table
client_KEYint (4)NOT NULLKey of company employee works for
employee_idnvarchar (22)NOT NULLClients employee identifier
social_security_numbernvarchar (22)NOT NULLSocial Security Number
first_namenvarchar (60)NOT NULLFirst name
middle_namenvarchar (60)NOT NULLMiddle name
last_namenvarchar (60)NOT NULLLast name
name_suffixnvarchar (10)NOT NULLName suffix (Jr, Sr, MD, etc.)
contact_KEYint (4)NOT NULLKey of parent contact record.
date_of_birthsmalldatetime (4)Date of birth
marital_status_type_KEYint (4)Marital status type
gender_type_KEYint (4)Gender type
race_type_KEYint (4)Race type
is_officerbit (1)NOT NULLIndicates if the employee is an officer of the company
is_probationarybit (1)NOT NULLIndicates if this employee is probationary
is_seasonalbit (1)NOT NULLIndicates if this employee is seasonal
is_family_of_ownerbit (1)NOT NULLIndicates if this employee is a member of the family that owns the company
w2_statutory_employeebit (1)NOT NULLIndicates if this is a W2 statutory employee
ste_location_codenvarchar (70)NOT NULLSymmetry Tax Engine generated code which links the employee taxing address to its related jurisdiction-related tax items
primary__client_pay_schedule_KEYint (4)Primary pay schedule
alternate__client_pay_schedule_KEYint (4)Alternate payschedule
is_paycard_recipientbit (1)NOT NULLIndicates if this employee is paid via deposits to a bankcard account
direct_deposit_method_KEYnvarchar (50)NOT NULLMethod used for Direct Deposit
direct_deposit_amount_percentdecimal (5, 2)NOT NULLPercentage of pay to be distrubted using direct deposit
direct_deposit_distribution_method_KEYint (4)NOT NULLIndicates if this employee direct deposit allocation method is Total Distribution (1) or Remainder of Distribution (2)
use_alternate_direct_deposit_distributionbit (1)NOT NULLEnables the use of an alternate set of direct deposit allocation distributions
distribute_pay_by_percentagebit (1)NOT NULLEnables an employee so that pay can be distributed among different work locations/departments
employee_id_sortablenvarchar (96)NOT NULLA 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_verifiedsmalldatetime (4)The date the ssn for this employee was verified.
date_new_hire_reportedsmalldatetime (4)The date this employee was included in a new hire report.
record_status_KEYint (4)NOT NULLThe current status for the employee record.
employee_type_KEYint (4)NOT NULLThe current type of the employee record.
use_backup_withholding_ratebit (1)NOT NULLIf true, then the employee uses the appropriate rate from the Backup_Withholding_Rate table.
current__employee_earning_type_KEYint (4)NOT NULLForeign key to the employee earning type table.
is_webemployee_portal_disabledbit (1)NOT NULLFlag to disable the employee WebEmployee portal.
is_webemployee_portal_activatedbit (1)NOT NULLFlag 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_emailbit (1)NOT NULLFlag to indicate if ESS needs to re-send a welcome email to the employee email address.
transmit_is_webemployee_portal_disabledbit (1)NOT NULLFlag to indicate that we need to push the portal disabled flag to ESS.
has_health_carebit (1)NOT NULLTure if this employee has health care which is provided by employer or from somewhere else.
export__quick_books_payee_KEYint (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_KEYint (4)NOT NULLThe selected method for determining how to calculate multi-state income tax withholding for the employee.
is_w2_retirement_planbit (1)NOT NULLIs this employee marked as having a retirement plan.
is_part_timebit (1)NOT NULLIndicates that the employee has part time employment status.
affordable_care_act_employee_status_KEYtinyint (1)NOT NULL
employee_versiontimestamp (8)NOT NULLThe version of this row. It's an aid in versioning the row.
employee_force_new_version_togglebit (1)NOT NULLA bit to toggle that causes the employee_version to update. Flip this bit when a subordinate tables are changed.
forced_webemployee_push_versiontinyint (1)NOT NULLAllows 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_securitybit (1)NOT NULLBit 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_creditbit (1)NOT NULLBit 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.
Primary key
NameColumnsDescription
PK_Employeeemployee_KEY
Unique constraints
NameColumnsDescription
UK_Employee$client_KEY$employee_idclient_KEY, employee_id
UK_Employee$client_KEY$employee_id_sortableclient_KEY, employee_id_sortable
UK_Employee$contact_KEYcontact_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Employee__Client__client_KEYclient_KEYClient (client_KEY)
FK_Employee__Client_Pay_Schedule__alternate__client_pay_schedule_KEYalternate__client_pay_schedule_KEYClient_Pay_Schedule (client_pay_schedule_KEY)
FK_Employee__Client_Pay_Schedule__primary__client_pay_schedule_KEYprimary__client_pay_schedule_KEYClient_Pay_Schedule (client_pay_schedule_KEY)
FK_Employee__Contact__contact_KEYcontact_KEYContact (contact_KEY)
FK_Employee__Affordable_Care_Act_Employee_Status__affordable_care_act_employee_status_KEYaffordable_care_act_employee_status_KEYAffordable_Care_Act_Employee_Status (affordable_care_act_employee_status_KEY)
FK_Employee__Direct_Deposit_Distribution_Method__direct_deposit_distribution_method_KEYdirect_deposit_distribution_method_KEYDirect_Deposit_Distribution_Method (direct_deposit_distribution_method_KEY)
FK_Employee__Direct_Deposit_Method__direct_deposit_method_KEYdirect_deposit_method_KEYDirect_Deposit_Method (direct_deposit_method_KEY)
FK_Employee__Employee_Earning_Type__current__employee_earning_type_KEYcurrent__employee_earning_type_KEYEmployee_Earning_Type (employee_earning_type_KEY)
FK_Employee__Employee_Multi_State_Income_Tax_Calculation_Method__employee_multi_state_income_tax_calculation_method_KEYemployee_multi_state_income_tax_calculation_method_KEYEmployee_Multi_State_Income_Tax_Calculation_Method (employee_multi_state_income_tax_calculation_method_KEY)
FK_Employee__Employee_Type__employee_type_KEYemployee_type_KEYEmployee_Type (employee_type_KEY)
FK_Employee__Gender_Type__gender_type_KEYgender_type_KEYGender_Type (gender_type_KEY)
FK_Employee__Marital_Status_Type__marital_status_type_KEYmarital_status_type_KEYMarital_Status_Type (marital_status_type_KEY)
FK_Employee__Quick_Books_Payee__export__quick_books_payee_KEYexport__quick_books_payee_KEYQuick_Books_Payee (quick_books_payee_KEY)
FK_Employee__Race_Type__race_type_KEYrace_type_KEYRace_Type (race_type_KEY)
FK_Employee__Record_Status__record_status_KEYrecord_status_KEYRecord_Status (record_status_KEY)
FK_Employee__Ste_Location_Code__ste_location_codeste_location_codeSte_Location_Code (ste_location_code)
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Employee_Self_Service_W2_Queue__Employee__employee_KEYemployee_KEYEmployee_Self_Service_W2_Queue
FK_Employee_W2_Rendered_Datashare__Employee__employee_KEYemployee_KEYEmployee_W2_Rendered_Datashare
FK_Custom_Value_Employee__Employee__employee_KEYemployee_KEYCustom_Value_Employee
FK_Employee_Benefit__Employee__employee_KEYemployee_KEYEmployee_Benefit
FK_Employee_Reconciliation_Tax_Form_Tax_Jurisdiction_Override__Employee__employee_KEYemployee_KEYEmployee_Reconciliation_Tax_Form_Tax_Jurisdiction_Override
FK_Garnishment_Order__Employee__employee_KEYemployee_KEYGarnishment_Order
FK_Form_1099__Employee__employee_KEYemployee_KEYForm_1099
FK_Employee_1095_Override__Employee__employee_KEYemployee_KEYEmployee_1095_Override
FK_Shortfall_Transaction__Employee__employee_KEYemployee_KEYShortfall_Transaction
FK_Employee_W2_Verification_Dates__Employee__employee_KEYemployee_KEYEmployee_W2_Verification_Dates
FK_Timerack_Employee_Update_Queue__Employee__employee_KEYemployee_KEYTimerack_Employee_Update_Queue
FK_Print_W2_Selection__Employee__employee_KEYemployee_KEYPrint_W2_Selection
FK_XactPay_Employee_SSN_History__Employee__employee_KEYemployee_KEYXactPay_Employee_SSN_History
FK_Quick_Books_Payee_N_Employee__Employee__employee_KEYemployee_KEYQuick_Books_Payee_N_Employee
FK_Payroll_Bank_Transaction__Employee__employee_KEYemployee_KEYPayroll_Bank_Transaction
FK_Ascii_Time_Clock_Employee_Id_Map__Employee__employee_KEYemployee_KEYAscii_Time_Clock_Employee_Id_Map
FK_Employee_Taxable_Wage_Adjustment__Employee__employee_KEYemployee_KEYEmployee_Taxable_Wage_Adjustment
FK_Employee_Other_State_Setting__Employee__employee_KEYemployee_KEYEmployee_Other_State_Setting
FK_Employee_W2_Box_12_Other_Item__Employee__employee_KEYemployee_KEYEmployee_W2_Box_12_Other_Item
FK_Employee_Self_Service_1095_Queue__Employee__employee_KEYemployee_KEYEmployee_Self_Service_1095_Queue
FK_Workforce_Hub_Employee_Update_Queue__Employee__employee_KEYemployee_KEYWorkforce_Hub_Employee_Update_Queue
FK_Employee_W2_Box_14_Other_Item__Employee__employee_KEYemployee_KEYEmployee_W2_Box_14_Other_Item
FK_Employee_W2_Tax_Jurisdiction_Override__Employee__employee_KEYemployee_KEYEmployee_W2_Tax_Jurisdiction_Override
FK_Employee_Work_Location_Payroll_Department__Employee__employee_KEYemployee_KEYEmployee_Work_Location_Payroll_Department
FK_Employee_Tax_Form_Locality_Amount_Override__Employee__employee_KEYemployee_KEYEmployee_Tax_Form_Locality_Amount_Override
FK_Employee_W2_Box_12_Override__Employee__employee_KEYemployee_KEYEmployee_W2_Box_12_Override
FK_Employee_Political_Subdivision_Code_Override__Employee__employee_KEYemployee_KEYEmployee_Political_Subdivision_Code_Override
FK_Employee_W2_Box_14_Override__Employee__employee_KEYemployee_KEYEmployee_W2_Box_14_Override
FK_Employee_W2_Override__Employee__employee_KEYemployee_KEYEmployee_W2_Override
FK_Print_1095_Selection__Employee__employee_KEYemployee_KEYPrint_1095_Selection
FK_Employee_Independent_Contractor_Reported__Employee__employee_KEYemployee_KEYEmployee_Independent_Contractor_Reported
FK_Workforce_Hub_Form_Queue__Employee__employee_KEYemployee_KEYWorkforce_Hub_Form_Queue
FK_Recorded_Event_Object_N_Employee__Employee__employee_KEYemployee_KEYRecorded_Event_Object_N_Employee
FK_Employee_W2_Rendered_Document__Employee__employee_KEYemployee_KEYEmployee_W2_Rendered_Document
FK_Employee_W4_Setting__Employee__employee_KEYemployee_KEYEmployee_W4_Setting
FK_Employee_Event__Employee__employee_KEYemployee_KEYEmployee_Event
FK_Employee_Payroll_Item__Employee__employee_KEYemployee_KEYEmployee_Payroll_Item
FK_Employee_Direct_Deposit_Allocation__Employee__employee_KEYemployee_KEYEmployee_Direct_Deposit_Allocation
FK_Employee_W2_Rendering_Queue__Employee__employee_KEYemployee_KEYEmployee_W2_Rendering_Queue
FK_Employee_Worker_Compensation_Item__Employee__employee_KEYemployee_KEYEmployee_Worker_Compensation_Item
FK_Pod_Service_W2_Queue__Employee__empoyee_KEYemployee_KEYPod_Service_W2_Queue
FK_Employee_1095__Employee__employee_KEYemployee_KEYEmployee_1095
FK_Employee_Tax_Jurisdiction__Employee__employee_KEYemployee_KEYEmployee_Tax_Jurisdiction
FK_Employee_W2_In_Process_Rendering_Queue__Employee__employee_KEYemployee_KEYEmployee_W2_In_Process_Rendering_Queue
FK_Web_Employee_Employee_Update_Queue__Employee__employee_KEYemployee_KEYWeb_Employee_Employee_Update_Queue
FK_Employee_Tip_Allocation__Employee__employee_KEYemployee_KEYEmployee_Tip_Allocation
FK_Employee_Tax_Item__Employee__employee_KEYemployee_KEYEmployee_Tax_Item
FK_Employee_Self_Service_1099_Queue__Employee__employee_KEYemployee_KEYEmployee_Self_Service_1099_Queue
FK_Payroll_Check_Payroll_Item_Import__Employee__employee_KEYemployee_KEYPayroll_Check_Payroll_Item_Import
FK_Payroll_Check_Tax_Item_Import__Employee__employee_KEYemployee_KEYPayroll_Check_Tax_Item_Import

Schema diagram