Table: dbo.Employee_Tip_Allocation
The table used to store employee-level tip allocation data and overrides.
Columns
NameTypeConstraintsDescription
employee_tip_allocation_KEYint (4)NOT NULLPrimary key for this table.
employee_KEYint (4)NOT NULLForeign key to the Employee table. Relationship is 0..M - 1 from this table to Employee.
employee_tip_total_KEYint (4)NOT NULLForeign key to the Employee_Tip_Total table. Relationship is M - 1 from this table to CTA.
sales_hoursdecimal (9, 2)NOT NULLEmployee's total sales for the year to date (if Client_Tip_Allocation.method is 0) or total hours (reg + ot + dt) associated with Tipped Wages pay items that the employee has worked for the year (if CTA.method is 1).
sales_hours_overridedecimal (9, 2)User-entered override amount that will take precedence over sales_hours (if non-null value).
direct_tips_reporteddecimal (9, 2)NOT NULLHolds the employee's total pay item Reported Tips amount for the year (takes precedence over sales_hours if not null).
direct_tips_reported_overridedecimal (9, 2)User-entered override amount that will take precedence over direct_tips_reported (if non-null value).
tips_to_allocatedecimal (9, 2)NOT NULLHolds the amount of tips to allocate (client level total) * shortfall % (see docs).
tips_to_allocate_overridedecimal (9, 2)User-entered override amount that will take precedence over tips_to_allocate (if non-null value).
Primary key
NameColumnsDescription
PK_Employee_Tip_Allocationemployee_tip_allocation_KEY
Unique constraints
NameColumnsDescription
UK_Employee_Tip_Allocation$employee_KEY$employee_tip_total_KEYemployee_KEY, employee_tip_total_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Employee_Tip_Allocation__Employee__employee_KEYemployee_KEYEmployee (employee_KEY)
FK_Employee_Tip_Allocation__Employee_Tip_Total__employee_tip_total_KEYemployee_tip_total_KEYEmployee_Tip_Total (employee_tip_total_KEY)

Schema diagram