Payroll Software - Need Help on Design Issues RRS feed

  • Question

  • Hi,
    I'm designing a Payroll application for my company.   I designed most of the tables and related them wherever required.  However, I stuck with Deduction table where I feel I need help from experts like you guys.  I will explain the table design briefly:

    Employee Table - Do have most of the common fields and is linked with Deduction Table and Deduction Table is further linked with Deduction Type.
    Employee Table                Deductions                                 DeductionTypes
    EmployeeID                         DeductionID                                 DeductionTypeID
    LastName                            EmployeeID                                  DeductionType
    FirstName                            DeductionTypeID                          
    ......                                    DedEffectiveDate
    Photo                                  DedEndDate
    DateofBirth                          DedNetAmt
    HomePhone                         DeductionInstallmentAmt

    My question is:
    How my application will automatically deduct the amount as per the Installment amount mentioned in the Deduction table.  Do I have to keep another table for inserting the deduction amount entry of each month? 

    Any help would be greatly appreciated.

    Thanks in advance.
    Wednesday, September 16, 2009 7:12 PM

All replies

  • Correct me if I am wrong, you need to add a record when ever an amount deducted from the employee salary per each month, if this is the case then your design is fine, you have an employee table and deduction type table, when ever there is a deduction from the employees salary, you will add a record to the deductions table considering that the employeeID, DeductTypeID, and the DedEffectiveDate are unique.

    Please mark answers for the posts that solve your problems.
    Thursday, September 17, 2009 1:50 AM

    Thank you for your reply.

    I think I have to explain to you the scenario in detail.

    This Payroll application should automate most of the manual works which is currently being done in the office.  

    Let me give an example for a deduction case.   One of the staff destroyed a costly equipment of the company.   The Officer evaluated the cost of equipment and found out that it worth USD 500.   We cannot deduct the whole amount (USD500) in one month salary.   Instead we will make it on installment basis. In this case let us assume USD 50 per month.  Please note that the   payroll frequency is monthly.

    Once the Total value (USD500) and Installment amount is entered in the system, on each month at the time of payroll, the amount of USD50/- should be automatically deducted.

    The user will enter the following dummy values ONLY ONE TIME  in the Deductions Table:

    Deduction ID   Employee ID     DeductionTypeID   DedEffectiveDate    DedEndDate    DedNetAmt    DeductionInstallmentAmt 

    D001                    10081           T005                    25/07/2009          25/12/2009              500                            50

    The DeductionType table is used only to store the following values:

    DeductionTypeID                                   DeductionType

    T001                                                    Salary Advance

    T002                                                    SuperMarket Purchase

    T005                                                    Equipment Value

    Given the scenario, my question is:

    Every month at the time of pay process, the application should automatically check this table and deduct the salary accordingly.

    The amount which has been deducted so shall be recorded somewhere. Where should I record the monthly deductions?  Is it in the same table or do I have to maintain a separate table for this?

    I’m ready to forward you the Entity relationship diagram. If required, please provide me your email address.

    Thanks & regards,



    Thursday, September 17, 2009 7:18 AM
  • Sorry for being very late :)

    Anyway, I prefer to have another table where lists the actual deduction amount per type, because for example the employee salary may be deducted for two or more different reasons, maybe the employee have deduction for equipment value and SuperMarket Purchase at the same time, right? at this point the employee will have nothing from his salary :).

    The new table will have the following schema.

    Deduction ID            <Row ID from Salaries Table per month>           Employee ID            Deduction Amoount        Effective Date


    The overall deductions from this table per employee per month can be recorded as a single column in the salaries table or it can be a computed column there.


    Send me the ER and I will send you the updated ones.

    Please mark answers for the posts that solve your problems.
    Wednesday, April 14, 2010 12:33 PM