Payroll System Database Design

Payroll System Table Relationship

Title: Payroll System Database Design


Automated Payroll system is an application that will manage and compute the employee’s salary. It connects information throughout the entire company. It is the easiest way to cater these needs. Computerize systems is typically simplify information, Quickly organize reports, automatically archive data, calculate deductions, easily track clock in/out and minimize manual efforts.

Simplified and quickly organize data is advantageous in many ways. Such as integrated the payroll figures into easy to read platform to ensure everything is accounted for, thus minimizing risks of valid or missed numbers. The systems include updating of records of the employees preparing payroll sheet & pay slips. Updating records is automatically updated and stored on host computers where it can be review or amended by a manager or supervisor to ensure the accuracy & consistency of the records of the employees. Preparing payroll sheet is a document which all information’s of employees are recorded including name, address, id, number, position of employee.

Pay slip is a slip of paper of an employee receives either as a notices, it will typically details the gross income and all taxes, and any other deductions such as retirement plan contributions, insurances contributions taken out of gross income to arrive at the final amount of the pay, including the year to date totals of all deductions of your salary in for circumstances.

Additional features of the system are the Backup and Restore utility which enables the users of the application to Backup and Restore the database

Database Schema with description of table:

tblaccount (id, username, password, accounttype)

tblemployee (id, barcode, lastname, firstname, middlename, dateofbirth, address, contact, status, positionid)

tblposition (id, positionname, dailyrate, monthlyrate, workingdayspermonth)

tblpayroll (id, employeeid, numofdayswork, bonus, overtimepay, grosssalary, cashadvance, latehours, absentdays, ssscontribution, philhealth, totaldeduction, netpay, payrollmonth, accountid)

Table Relationship:

Payroll System Table Relationship

Payroll System Table Relationship

Sample Queries:

The query below will display the list of employees together with their position, monthly salary, total deduction and net salary.

SELECT tblemployee.barcode, tblemployee.lastname, tblemployee.firstname, tblposition.PositionName, tblposition.monthlyrate, tblpayroll.grosssalary, tblpayroll.totaldeduction, tblpayroll.netpay

FROM tblposition INNER JOIN (tblemployee INNER JOIN (tblaccount INNER JOIN tblpayroll ON = tblpayroll.accountid) ON = tblpayroll.emplooyeeid) ON = tblemployee.positionid

GROUP BY tblemployee.barcode, tblemployee.lastname, tblemployee.firstname, tblposition.PositionName, tblposition.monthlyrate, tblpayroll.grosssalary, tblpayroll.totaldeduction, tblpayroll.netpay;

Development Tools:

Visual Basic and MySQL


you can leave your comments, suggestions and questions on the comment box below or you may visit our facebook page.