Parsed Script Layout

U Salary Template Masters Employee Fk fk_AllowanceDeduction_AllowanceDeduction AllowanceDeduction ref AllowanceDeduction ( allowanceDeductionID ) Fk fk_AllowanceDeduction_AllowanceDeduction AllowanceDeduction ref AllowanceDeduction ( allowanceDeductionID ) allowanceDeductionID Fk fk_AllowanceDeduction_SpecialAllowanceDeduction AllowanceDeduction ref SpecialAllowanceDeduction ( allowanceDeductionID ) Fk fk_AllowanceDeduction_SpecialAllowanceDeduction AllowanceDeduction ref SpecialAllowanceDeduction ( allowanceDeductionID ) allowanceDeductionID Fk fk_BasicInfo_AllowanceDeduction BasicInfo ref AllowanceDeduction ( templateID ) Fk fk_BasicInfo_AllowanceDeduction BasicInfo ref AllowanceDeduction ( templateID ) templateID Fk fk_BasicInfo_SpecialAllowanceDeduction BasicInfo ref SpecialAllowanceDeduction ( templateID ) Fk fk_BasicInfo_SpecialAllowanceDeduction BasicInfo ref SpecialAllowanceDeduction ( templateID ) templateID Fk fk_BasicInfo_Attendance BasicInfo ref Attendance ( employeeID ) Fk fk_BasicInfo_Attendance BasicInfo ref Attendance ( employeeID ) employeeID Fk fk_BasicInfo_Holiday BasicInfo ref Holiday ( employeeID ) Fk fk_BasicInfo_Holiday BasicInfo ref Holiday ( employeeID ) employeeID Fk fk_BasicInfo_Leave BasicInfo ref Leave ( employeeID ) Fk fk_BasicInfo_Leave BasicInfo ref Leave ( employeeID ) employeeID Fk fk_BasicInfo_AllottedLeaves BasicInfo ref AllottedLeaves ( employeeID ) Fk fk_BasicInfo_AllottedLeaves BasicInfo ref AllottedLeaves ( employeeID ) employeeID Fk fk_BasicInfo_SalaryAdvance BasicInfo ref SalaryAdvance ( employeeID ) Fk fk_BasicInfo_SalaryAdvance BasicInfo ref SalaryAdvance ( employeeID ) employeeID Fk fk_BasicInfo_SalaryAdvanceRepayment BasicInfo ref SalaryAdvanceRepayment ( employeeID ) Fk fk_BasicInfo_SalaryAdvanceRepayment BasicInfo ref SalaryAdvanceRepayment ( employeeID ) employeeID Fk fk_BasicInfo_Finalized BasicInfo ref Finalized ( employeeID ) Fk fk_BasicInfo_Finalized BasicInfo ref Finalized ( employeeID ) employeeID Fk fk_BasicInfo_Settings BasicInfo ref Settings ( employeeID ) Fk fk_BasicInfo_Settings BasicInfo ref Settings ( employeeID ) employeeID Fk fk_Leave_LeaveDetail Leave ref LeaveDetail ( employeeLeaveID ) Fk fk_Leave_LeaveDetail Leave ref LeaveDetail ( employeeLeaveID ) employeeLeaveID Fk fk_Leave_AllottedLeaves Leave ref AllottedLeaves ( leaveTypeID ) Fk fk_Leave_AllottedLeaves Leave ref AllottedLeaves ( leaveTypeID ) leaveTypeID Fk fk_Location_Shift Location ref Shift ( locationID ) Fk fk_Location_Shift Location ref Shift ( locationID ) locationID Fk fk_Location_Holiday Location ref Holiday ( locationID ) Fk fk_Location_Holiday Location ref Holiday ( locationID ) locationID Fk fk_Location_Attendance Location ref Attendance ( locationID ) Fk fk_Location_Attendance Location ref Attendance ( locationID ) locationID Fk fk_SalaryAdvance_SalaryAdvanceRepayment SalaryAdvance ref SalaryAdvanceRepayment ( salaryAdvanceID ) Fk fk_SalaryAdvance_SalaryAdvanceRepayment SalaryAdvance ref SalaryAdvanceRepayment ( salaryAdvanceID ) salaryAdvanceID Fk fk_Shift_BasicInfo Shift ref BasicInfo ( shiftID ) Fk fk_Shift_BasicInfo Shift ref BasicInfo ( shiftID ) shiftID AllottedLeavesTable Employee.AllottedLeaves Unq pk_AllottedLeaves ( allottedLeaveID ) allottedLeaveIDallottedLeaveID int # Unq unq_AllottedLeaves_employeeID ( employeeID ) employeeIDemployeeID int Referred by BasicInfo ( employeeID ) Unq unq_AllottedLeaves_leaveTypeID ( leaveTypeID ) leaveTypeIDleaveTypeID int Referred by Leave ( leaveTypeID ) leaveCountleaveCount decimal(18,2) # leavesTakenleavesTaken decimal(18,2) # yearyear int # createdDatecreatedDate datetimeoffset(7) AllowanceDeductionTable Template.AllowanceDeduction Unq pk_AllowanceDeduction ( templateAllowanceDeductionID ) templateAllowanceDeductionIDtemplateAllowanceDeductionID int # Unq unq_AllowanceDeduction_allowanceDeductionID ( allowanceDeductionID ) allowanceDeductionIDallowanceDeductionID int # allowanceOrDeductionallowanceOrDeduction varchar(20) t amountPercentageManualamountPercentageManual varchar(20) t amountPercentageamountPercentage decimal(18,2) # considerInESIconsiderInESI varchar(20) t Unq unq_AllowanceDeduction_templateID ( templateID ) templateIDtemplateID int Referred by BasicInfo ( templateID ) AllowanceDeductionTable Master.AllowanceDeduction Unq pk_AllowanceDeduction ( allowanceDeductionID ) allowanceDeductionIDallowanceDeductionID int References AllowanceDeduction ( allowanceDeductionID ) References SpecialAllowanceDeduction ( allowanceDeductionID ) allowanceDeductionTypeNameallowanceDeductionTypeName varchar(100) t regularOrSpecialregularOrSpecial varchar(20) t allowanceDeductionStatusallowanceDeductionStatus varchar(20) t AttendanceTable Employee.Attendance Unq pk_Attendance ( attendanceID ) attendanceIDattendanceID int # Unq unq_Attendance_employeeID ( employeeID ) employeeIDemployeeID int Referred by BasicInfo ( employeeID ) Unq unq_Attendance_locationID ( locationID ) locationIDlocationID int Referred by Location ( locationID ) loginTimeloginTime datetimeoffset(7) attendanceDateattendanceDate date d logoutTimelogoutTime datetimeoffset(7) totalHourstotalHours decimal(18,2) # loginStatusloginStatus varchar(20) t BasicInfoTable Template.BasicInfo Unq pk_BasicInfo ( templateID ) templateIDtemplateID int References AllowanceDeduction ( templateID ) References SpecialAllowanceDeduction ( templateID ) templateNametemplateName varchar(100) t leaveWageAllowedleaveWageAllowed varchar(20) t pfAllowedpfAllowed varchar(20) t otAllowedotAllowed varchar(20) t esiAllowedesiAllowed varchar(20) t basicbasic money # dada money # sAAndOtherssAAndOthers money # basicDAAmountPerDaybasicDAAmountPerDay money # calculateAllowanceDeductionFromBasiccalculateAllowanceDeductionFromBasic varchar(20) t calculateAllowanceDeductionFromBasicDAcalculateAllowanceDeductionFromBasicDA varchar(20) t calculateAllowanceDeductionFromBasicDASAcalculateAllowanceDeductionFromBasicDASA varchar(20) t BasicInfoTable Employee.BasicInfo Unq pk_BasicInfo ( employeeID ) employeeIDemployeeID int References Attendance ( employeeID ) References Holiday ( employeeID ) References Leave ( employeeID ) References AllottedLeaves ( employeeID ) References SalaryAdvance ( employeeID ) References SalaryAdvanceRepayment ( employeeID ) References Finalized ( employeeID ) References Settings ( employeeID ) employeeNameemployeeName varchar(100) t contactNumbercontactNumber varchar(20) t employeeCodeemployeeCode varchar(20) t addressaddress varchar(200) t OTHERINFOOTHERINFO varchar(400) t Unq unq_BasicInfo_shiftID ( shiftID ) shiftIDshiftID int Referred by Shift ( shiftID ) DesignationTable Master.Designation Unq pk_Designation ( designationID ) designationIDdesignationID int # designationNamedesignationName varchar(50) t designationStatusdesignationStatus varchar(20) t FinalizedTable Salary.Finalized Unq pk_Finalized ( salaryFinalizedID ) salaryFinalizedIDsalaryFinalizedID int # Unq unq_Finalized_employeeID ( employeeID ) employeeIDemployeeID int # basicPaybasicPay money # grossAllowancegrossAllowance money # grossDeductionsgrossDeductions money # loplop money # netPaidnetPaid money # monthmonth int # yearyear int # createDatecreateDate datetimeoffset(7) userIDuserID int # dada money # sasa money # overTimeoverTime money # grossPayablegrossPayable money # totalAttendancetotalAttendance decimal(18,2) # GeneratedTable Salary.Generated Unq pk_Generated ( generatedSalaryID ) generatedSalaryIDgeneratedSalaryID int # yearyear int # monthmonth int # salaryDataJSONsalaryDataJSON nvarchar(max) t createdUserIDcreatedUserID int # createdDateTimecreatedDateTime datetimeoffset(7) HolidayTable Master.Holiday Unq pk_Holiday ( holidayCalendarID ) holidayCalendarIDholidayCalendarID int # holidayNameholidayName varchar(100) t holidayDateholidayDate date d Unq unq_Holiday_locationID ( locationID ) locationIDlocationID int Referred by Location ( locationID ) HolidayTable Employee.Holiday Unq pk_Holiday ( employeeHolidayID ) employeeHolidayIDemployeeHolidayID int # Unq unq_Holiday_employeeID ( employeeID ) employeeIDemployeeID int Referred by BasicInfo ( employeeID ) holidayNameholidayName varchar(200) t holidayDateholidayDate date d holidayStatusholidayStatus varchar(20) t LeaveTable Employee.Leave Unq pk_Leave ( employeeLeaveID ) employeeLeaveIDemployeeLeaveID int References LeaveDetail ( employeeLeaveID ) Unq unq_Leave_employeeID ( employeeID ) employeeIDemployeeID int Referred by BasicInfo ( employeeID ) leaveFromleaveFrom datetimeoffset(7) leaveToleaveTo datetimeoffset(7) reasonreason varchar(200) t leaveStatusleaveStatus varchar(20) t leaveApproveUserIDleaveApproveUserID int # leaveApprovedDateleaveApprovedDate datetimeoffset(7) userIDuserID int # createdDateimecreatedDateime datetimeoffset(7) rejectedReasonrejectedReason varchar(200) t leaveModeleaveMode varchar(30) t numberOfLeavesnumberOfLeaves decimal(18,2) # LeaveTable Master.Leave Unq pk_Leave ( leaveTypeID ) leaveTypeIDleaveTypeID int References AllottedLeaves ( leaveTypeID ) leaveTypeNameleaveTypeName varchar(50) t leaveTypeStatusleaveTypeStatus varchar(20) t LeaveDetailTable Employee.LeaveDetail Unq pk_LeaveDetail ( employeeLeaveDetailID ) employeeLeaveDetailIDemployeeLeaveDetailID int # Unq unq_LeaveDetail_employeeLeaveID ( employeeLeaveID ) employeeLeaveIDemployeeLeaveID int Referred by Leave ( employeeLeaveID ) leaveDateleaveDate date d approveStatusapproveStatus varchar(20) t createdUserIDcreatedUserID int # createdDateTimecreatedDateTime datetimeoffset(7) leaveModeleaveMode varchar(20) t leaveleave decimal(18,2) # LocationTable Master.Location Unq pk_Location ( locationID ) locationIDlocationID int References Shift ( locationID ) References Holiday ( locationID ) References Attendance ( locationID ) locationNamelocationName varchar(50) t countrycountry varchar(100) t SalaryAdvanceTable Employee.SalaryAdvance Unq pk_SalaryAdvance ( salaryAdvanceID ) salaryAdvanceIDsalaryAdvanceID int References SalaryAdvanceRepayment ( salaryAdvanceID ) Unq unq_SalaryAdvance_employeeID ( employeeID ) employeeIDemployeeID int Referred by BasicInfo ( employeeID ) paymentDatepaymentDate datetimeoffset(7) advanceAmountadvanceAmount money # createdUserIDcreatedUserID int # createdDatecreatedDate datetimeoffset(7) outstandingAmountoutstandingAmount money # salaryAdvanceStatussalaryAdvanceStatus varchar(20) t repaymentCompletedDaterepaymentCompletedDate datetimeoffset(7) repayinMonthsrepayinMonths int # perMonthAmountperMonthAmount money # SalaryAdvanceRepaymentTable Employee.SalaryAdvanceRepayment Unq pk_SalaryAdvanceRepayment ( salaryAdvanceRepaymentID ) salaryAdvanceRepaymentIDsalaryAdvanceRepaymentID int # Unq unq_SalaryAdvanceRepayment_salaryAdvanceID ( salaryAdvanceID ) salaryAdvanceIDsalaryAdvanceID int Referred by SalaryAdvance ( salaryAdvanceID ) Unq unq_SalaryAdvanceRepayment_employeeID ( employeeID ) employeeIDemployeeID int Referred by BasicInfo ( employeeID ) monthmonth int # yearyear int # repaymentAmountrepaymentAmount money # createdUserIDcreatedUserID int # createdDatecreatedDate datetimeoffset(7) SettingsTable Salary.Settings Unq pk_Settings ( salarySettingsID ) salarySettingsIDsalarySettingsID int # basicSalarybasicSalary money # dada money # overtimeAllowedovertimeAllowed varchar(20) t holidayWageAllowedholidayWageAllowed varchar(20) t esiAllowedesiAllowed varchar(20) t calculateAllowanceDeductionFromBasiccalculateAllowanceDeductionFromBasic varchar(20) t calculateAllowanceDeductionFromBasicDAcalculateAllowanceDeductionFromBasicDA varchar(20) t Unq unq_Settings_employeeID ( employeeID ) employeeIDemployeeID int # remarksremarks varchar(500) t offTypeoffType varchar t calculateAllowanceDeductionFromBasicDASAcalculateAllowanceDeductionFromBasicDASA varchar(20) t basicDAAmountPerDaybasicDAAmountPerDay varchar(20) t pfAllowedpfAllowed varchar(20) t ShiftTable Master.Shift Unq pk_Shift ( shiftID ) shiftIDshiftID int References BasicInfo ( shiftID ) shiftCodeshiftCode varchar(20) t shiftStartTimeshiftStartTime time(7) shiftEndTimeshiftEndTime time(7) shiftHoursshiftHours int # Unq unq_Shift_locationID ( locationID ) locationIDlocationID int Referred by Location ( locationID ) SpecialAllowanceDeductionTable Template.SpecialAllowanceDeduction Unq pk_SpeicalAllowanceDeduction ( specialAllowanceDeductionID ) specialAllowanceDeductionIDspecialAllowanceDeductionID int # Unq unq_SpecialAllowanceDeduction_templateID ( templateID ) templateIDtemplateID int Referred by BasicInfo ( templateID ) allowanceOrDeductionallowanceOrDeduction varchar(20) t amountOrPercentageamountOrPercentage varchar(20) t amountPercentageValueamountPercentageValue decimal(18,2) # monthmonth int # yearyear int # recurringrecurring varchar(10) t considerInESIconsiderInESI varchar(10) t Unq unq_SpecialAllowanceDeduction_allowanceDeductionID ( allowanceDeductionID ) allowanceDeductionIDallowanceDeductionID int # Current layout is generated by parsing the SQL script. DbSchema is in 'offline' mode (not connected to any database). Current layout is generated by parsing the SQL script. DbSchema is in 'offline' mode (not connected to any database). Current layout is generated by parsing the SQL script. DbSchema is in 'offline' mode (not connected to any database).


Schema Payroll.Employee
Table AllottedLeaves
IdxField NameData Type
allottedLeaveID int IDENTITY (1, 1)
employeeID int
leaveTypeID int
  leaveCount decimal(18,2)
  leavesTaken decimal(18,2)
  year int
  createdDate datetimeoffset(7)
Indexes
pk_AllottedLeaves ON allottedLeaveID
unq_AllottedLeaves_employeeID ON employeeID
unq_AllottedLeaves_leaveTypeID ON leaveTypeID
Referring Foreign Keys
fk_BasicInfo_AllottedLeaves (employeeID) ← BasicInfo
fk_Leave_AllottedLeaves (leaveTypeID) ← Leave


Schema Payroll.Template
Table AllowanceDeduction
IdxField NameData Type
templateAllowanceDeductionID int IDENTITY (1, 1)
allowanceDeductionID int
  allowanceOrDeduction varchar(20)
  amountPercentageManual varchar(20)
  amountPercentage decimal(18,2)
  considerInESI varchar(20)
templateID int
Indexes
pk_AllowanceDeduction ON templateAllowanceDeductionID
unq_AllowanceDeduction_templateID ON templateID
unq_AllowanceDeduction_allowanceDeductionID ON allowanceDeductionID
Referring Foreign Keys
fk_BasicInfo_AllowanceDeduction (templateID) ← BasicInfo
fk_AllowanceDeduction_AllowanceDeduction (allowanceDeductionID) ← AllowanceDeduction


Schema Payroll.Master
Table AllowanceDeduction
IdxField NameData Type
allowanceDeductionID int IDENTITY (1, 1)
  allowanceDeductionTypeName varchar(100)
  regularOrSpecial varchar(20)
  allowanceDeductionStatus varchar(20)
Indexes
pk_AllowanceDeduction ON allowanceDeductionID
Foreign Keys
fk_AllowanceDeduction_AllowanceDeduction (allowanceDeductionID) → AllowanceDeduction
fk_AllowanceDeduction_SpecialAllowanceDeduction (allowanceDeductionID) → SpecialAllowanceDeduction


Table Attendance
IdxField NameData Type
attendanceID int IDENTITY (1, 1)
employeeID int
locationID int
  loginTime datetimeoffset(7)
  attendanceDate date
  logoutTime datetimeoffset(7)
  totalHours decimal(18,2)
  loginStatus varchar(20)
Indexes
pk_Attendance ON attendanceID
unq_Attendance_employeeID ON employeeID
unq_Attendance_locationID ON locationID
Referring Foreign Keys
fk_BasicInfo_Attendance (employeeID) ← BasicInfo
fk_Location_Attendance (locationID) ← Location


Table BasicInfo
IdxField NameData Type
templateID int IDENTITY (1, 1)
  templateName varchar(100)
  leaveWageAllowed varchar(20)
  pfAllowed varchar(20)
  otAllowed varchar(20)
  esiAllowed varchar(20)
  basic money
  da money
  sAAndOthers money
  basicDAAmountPerDay money
  calculateAllowanceDeductionFromBasic varchar(20)
  calculateAllowanceDeductionFromBasicDA varchar(20)
  calculateAllowanceDeductionFromBasicDASA varchar(20)
Indexes
pk_BasicInfo ON templateID
Foreign Keys
fk_BasicInfo_AllowanceDeduction (templateID) → AllowanceDeduction
fk_BasicInfo_SpecialAllowanceDeduction (templateID) → SpecialAllowanceDeduction


Table BasicInfo
IdxField NameData Type
employeeID int IDENTITY (1, 1)
  employeeName varchar(100)
  contactNumber varchar(20)
  employeeCode varchar(20)
  address varchar(200)
  OTHERINFO varchar(400)
shiftID int
Indexes
pk_BasicInfo ON employeeID
unq_BasicInfo_shiftID ON shiftID
Foreign Keys
fk_BasicInfo_Attendance (employeeID) → Attendance
fk_BasicInfo_Holiday (employeeID) → Holiday
fk_BasicInfo_Leave (employeeID) → Leave
fk_BasicInfo_AllottedLeaves (employeeID) → AllottedLeaves
fk_BasicInfo_SalaryAdvance (employeeID) → SalaryAdvance
fk_BasicInfo_SalaryAdvanceRepayment (employeeID) → SalaryAdvanceRepayment
fk_BasicInfo_Finalized (employeeID) → Finalized
fk_BasicInfo_Settings (employeeID) → Settings
Referring Foreign Keys
fk_Shift_BasicInfo (shiftID) ← Shift


Table Designation
IdxField NameData Type
designationID int IDENTITY (1, 1)
  designationName varchar(50)
  designationStatus varchar(20)
Indexes
pk_Designation ON designationID


Schema Payroll.Salary
Table Finalized
IdxField NameData Type
salaryFinalizedID int IDENTITY (1, 1)
employeeID int
  basicPay money
  grossAllowance money
  grossDeductions money
  lop money
  netPaid money
  month int
  year int
  createDate datetimeoffset(7)
  userID int
  da money
  sa money
  overTime money
  grossPayable money
  totalAttendance decimal(18,2)
Indexes
pk_Finalized ON salaryFinalizedID
unq_Finalized_employeeID ON employeeID
Referring Foreign Keys
fk_BasicInfo_Finalized (employeeID) ← BasicInfo


Table Generated
IdxField NameData Type
generatedSalaryID int IDENTITY (1, 1)
  year int
  month int
  salaryDataJSON nvarchar(max)
  createdUserID int
  createdDateTime datetimeoffset(7)
Indexes
pk_Generated ON generatedSalaryID


Table Holiday
IdxField NameData Type
holidayCalendarID int IDENTITY (1, 1)
  holidayName varchar(100)
  holidayDate date
locationID int
Indexes
pk_Holiday ON holidayCalendarID
unq_Holiday_locationID ON locationID
Referring Foreign Keys
fk_Location_Holiday (locationID) ← Location


Table Holiday
IdxField NameData Type
employeeHolidayID int IDENTITY (1, 1)
employeeID int
  holidayName varchar(200)
  holidayDate date
  holidayStatus varchar(20)
Indexes
pk_Holiday ON employeeHolidayID
unq_Holiday_employeeID ON employeeID
Referring Foreign Keys
fk_BasicInfo_Holiday (employeeID) ← BasicInfo


Table Leave
IdxField NameData Type
employeeLeaveID int IDENTITY (1, 1)
employeeID int
  leaveFrom datetimeoffset(7)
  leaveTo datetimeoffset(7)
  reason varchar(200)
  leaveStatus varchar(20)
  leaveApproveUserID int
  leaveApprovedDate datetimeoffset(7)
  userID int
  createdDateime datetimeoffset(7)
  rejectedReason varchar(200)
  leaveMode varchar(30)
  numberOfLeaves decimal(18,2)
Indexes
pk_Leave ON employeeLeaveID
unq_Leave_employeeID ON employeeID
Foreign Keys
fk_Leave_LeaveDetail (employeeLeaveID) → LeaveDetail
Referring Foreign Keys
fk_BasicInfo_Leave (employeeID) ← BasicInfo


Table Leave
IdxField NameData Type
leaveTypeID int IDENTITY (1, 1)
  leaveTypeName varchar(50)
  leaveTypeStatus varchar(20)
Indexes
pk_Leave ON leaveTypeID
Foreign Keys
fk_Leave_AllottedLeaves (leaveTypeID) → AllottedLeaves


Table LeaveDetail
IdxField NameData Type
employeeLeaveDetailID int IDENTITY (1, 1)
employeeLeaveID int
  leaveDate date
  approveStatus varchar(20)
  createdUserID int
  createdDateTime datetimeoffset(7)
  leaveMode varchar(20)
  leave decimal(18,2)
Indexes
pk_LeaveDetail ON employeeLeaveDetailID
unq_LeaveDetail_employeeLeaveID ON employeeLeaveID
Referring Foreign Keys
fk_Leave_LeaveDetail (employeeLeaveID) ← Leave


Table Location
IdxField NameData Type
locationID int IDENTITY (1, 1)
  locationName varchar(50)
  country varchar(100)
Indexes
pk_Location ON locationID
Foreign Keys
fk_Location_Shift (locationID) → Shift
fk_Location_Holiday (locationID) → Holiday
fk_Location_Attendance (locationID) → Attendance


Table SalaryAdvance
IdxField NameData Type
salaryAdvanceID int IDENTITY (1, 1)
employeeID int
  paymentDate datetimeoffset(7)
  advanceAmount money
  createdUserID int
  createdDate datetimeoffset(7)
  outstandingAmount money
  salaryAdvanceStatus varchar(20)
  repaymentCompletedDate datetimeoffset(7)
  repayinMonths int
  perMonthAmount money
Indexes
pk_SalaryAdvance ON salaryAdvanceID
unq_SalaryAdvance_employeeID ON employeeID
Foreign Keys
fk_SalaryAdvance_SalaryAdvanceRepayment (salaryAdvanceID) → SalaryAdvanceRepayment
Referring Foreign Keys
fk_BasicInfo_SalaryAdvance (employeeID) ← BasicInfo


Table SalaryAdvanceRepayment
IdxField NameData Type
salaryAdvanceRepaymentID int IDENTITY (1, 1)
salaryAdvanceID int
employeeID int
  month int
  year int
  repaymentAmount money
  createdUserID int
  createdDate datetimeoffset(7)
Indexes
pk_SalaryAdvanceRepayment ON salaryAdvanceRepaymentID
unq_SalaryAdvanceRepayment_employeeID ON employeeID
unq_SalaryAdvanceRepayment_salaryAdvanceID ON salaryAdvanceID
Referring Foreign Keys
fk_BasicInfo_SalaryAdvanceRepayment (employeeID) ← BasicInfo
fk_SalaryAdvance_SalaryAdvanceRepayment (salaryAdvanceID) ← SalaryAdvance


Table Settings
IdxField NameData Type
salarySettingsID int IDENTITY (1, 1)
  basicSalary money
  da money
  overtimeAllowed varchar(20)
  holidayWageAllowed varchar(20)
  esiAllowed varchar(20)
  calculateAllowanceDeductionFromBasic varchar(20)
  calculateAllowanceDeductionFromBasicDA varchar(20)
employeeID int
  remarks varchar(500)
  offType varchar
  calculateAllowanceDeductionFromBasicDASA varchar(20)
  basicDAAmountPerDay varchar(20)
  pfAllowed varchar(20)
Indexes
pk_Settings ON salarySettingsID
unq_Settings_employeeID ON employeeID
Referring Foreign Keys
fk_BasicInfo_Settings (employeeID) ← BasicInfo


Table Shift
IdxField NameData Type
shiftID int IDENTITY (1, 1)
  shiftCode varchar(20)
  shiftStartTime time(7)
  shiftEndTime time(7)
  shiftHours int
locationID int
Indexes
pk_Shift ON shiftID
unq_Shift_locationID ON locationID
Foreign Keys
fk_Shift_BasicInfo (shiftID) → BasicInfo
Referring Foreign Keys
fk_Location_Shift (locationID) ← Location


Table SpecialAllowanceDeduction
IdxField NameData Type
specialAllowanceDeductionID int IDENTITY (1, 1)
templateID int
  allowanceOrDeduction varchar(20)
  amountOrPercentage varchar(20)
  amountPercentageValue decimal(18,2)
  month int
  year int
  recurring varchar(10)
  considerInESI varchar(10)
allowanceDeductionID int
Indexes
pk_SpeicalAllowanceDeduction ON specialAllowanceDeductionID
unq_SpecialAllowanceDeduction_templateID ON templateID
unq_SpecialAllowanceDeduction_allowanceDeductionID ON allowanceDeductionID
Referring Foreign Keys
fk_BasicInfo_SpecialAllowanceDeduction (templateID) ← BasicInfo
fk_AllowanceDeduction_SpecialAllowanceDeduction (allowanceDeductionID) ← AllowanceDeduction