Payroll ERD Design Issue RRS feed

  • Question

  • Hi,
    I'm designing a Payroll application for my company should automate most of the manual works which is currently being done in the office.  I designed most of the tables and related them wherever required. Some of the tables in the above ERD has been removed  purposely. 

    Now I stuck with PAYROLL TABLE.

    My doubts are as follows:

    1) How can I get detailed record of allowances/deductions using the Payroll table.  I do have seperate Allowance Transaction and Deduction Transaction Tables. By using the above design, I doubt that I made a mistake somewhere in the design. Do I have to connect the Payroll table with Deduction/Allowance Transaction table?

    2) I do not need sum of the allowances/deductions in the Payroll slip. I should get a very detailed report on the slip. For e.g . 
       House Allowance : USD.500.000
       Car Allowance:    USD.200.000
       Social Insurance: USD. 50.000

    By using the above design, will I be able to generate a detailed slip?

    I expect your help.

    Thanks & regards,


    Thursday, September 17, 2009 2:39 PM


  • At a glance, you should be able to get the deductions and allowances that were current for a given payroll period.  Since each of these tables are already related to an employee, as is payroll to employee, it appears that this should work as it is designed.

    To get the allowances for a given payroll record:

    select PayDate, BasicPay, NetPay, a.AllowanceAmt
    from Payroll p inner join Employees e on p.EmployeeID = e.EmployeeID
    left outer join Allowances a on e.EmployeeID = a.EmployeeID
    where a.AllowanceID = @TheAllowanceID and p.PayDate between a.EffectiveDate and a.EndDate

    For employees without allowance records, the date range I used in the WHERE clause may not return results so the logic may have to be a little more complex.

    As a matter of convention, tables are usually given singular, rather than plural names (Employee rather than Employees).  This is an arguable point.  Just be consistent.

    You have quite a bit of inconsistency in your attribute names  (ID, Id, Date, _date).  Since you're in a design phase, it would be good to clean these up.

    Paul Turley, MVP, *Please vote if helpful*
    Wednesday, July 13, 2011 7:12 AM