Payroll System Design
Kiliano Ratha
The salary for an employee is made up of various salary components.Each salary component has 3 rules associated with it, a Calculation Rule (Calculate component as % of another component, or % of a fixed number or a fixed number), an Eligibility Rule (whether an Employee/Dept is eligible for a component) and a Constraint Rule which limits tha max and min of a component.
The client will be generating payroll for multiple clients each hosting a Separate Database instance. They may each have a different interpretation of each component and may have different components.
Some people are advocating magic SPs which will take an employee Id and generate a payroll for that month.Others want the logic to be split into separate components which will get the dependent data for an employee in the application layer and calculate these components there.
Will SPs cause a mess here, as the rules suggested above will be specified by the end user and will need to be customizable through a web UI.We will have to parse the formulas from SQL. How difficult or easy will it be?What advantage will doing this in the Application Layer (C# .Net) have over using SPs?
My only suggestion after looking at that is to check out the strategy pattern from the GoF design patterns book. You'd probably want the strategies done in a scripting language rather than your main compiled language though as then you will find editing them easier.
Some people are advocating magic SPs which will take an employee Id and generate a payroll for that month. Others want the logic to be split into separate components which will get the dependent data for an employee in the application layer and calculate these components there.
If payroll generation is an offline activity, then I would do it in sps and run them from scheduled jobs. There is no reason the application should be involved in this at all. If you want them to have the ability to run payroll out of schedule, the application could call the same sp.
Security issues you really need to consider in doing payroll:Do not anywhere use dynamic sql. No users should have direct rights to tables. All rights should be at the sp level (and no dynamic sql inthe sps either or you have to set rights at the table level). This is to limit the abilty of users to commit payroll fraud and is extremely important. Any payroll system that uses any dynamic sql is at risk for company employees to commit fraud by directly accessing the tables and doing things the application won't let them do. This is one reason why I would not accept any answer to your question except to use sps.
Business rules should be enforced at the database level for the same reason. You do not want someone using a query tool to add or change data that doesn't follow your business rules. This is extremely critical in payroll (or any other financial system). Use constraints where possible, triggers if the logic is more complex.
Be very careful about which users can change the payroll calculation. Again security is the real issue here. Just because I'm a supervisor doesn't mean I should be able to change any employee's salary. This is easy to get wrong, so be very careful here.
The all SP approach is likely to give you the best performance if your processing is uniform. This will allow you to take full advantage of the database caches and avoid the network bottleneck. Beware of cursors and queries with join criteria different than equals/not-equal (if you have these, the processing is usually better handled by a general purpose language).
If you go for an application, consider using rule management system to encode the rules outside of the database. This would give you maximum transparency and flexibility. Good free rule engines for Java is Drools (some people also like Jess which is a CLIPS clone). Not sure what are the .Net offerings, but in the worst case you can wrap Drools in a web service and use it from C#.
In another study, Efendi [7] presented research on the implementation of the Civil Servant Salary SIM system in the Administrative Service Office of the Education Unit in Grogol District, Sukoharjo Regency. The author utilized a combination of interviews, application observation, and literature review to assess the efficiency and effectiveness of the computerized payroll management system. The system was developed using the online-offline model, with the PHP programming language and functional components such as administration and operations, management reporting, databases, search, and data management. The results showed that the application provided a more efficient, accurate, and effective solution compared to previous manual methods, allowing for the calculation and updating of salary data online, and implementing security measures through user authentication. In a recent contribution to the field, Rumetna et al. [8] aimed to design a payroll information system for the Sorong Islands District office. The Waterfall method was used as the development model and it consisted of 5 stages: requirements definition, system and software design, implementation and unit testing, integration and system testing, and operation and maintenance. The study was carried out by collecting data through direct interviews with 4 resource persons and by using various tools such as Unified Modeling Language, Macromedia Dreamweaver CS 6, and XAMPP. The system was tested using the Black Box Testing technique. The results showed that the payroll information system met the requirements of the Sorong Islands District office and was able to process employee salary data efficiently.
Our proposed system is distinct from prior works in terms of electronic auditing, making the audit process more efficient. Its flexible design makes it suitable for all types of organizations, as new units, allowances, and deductions can be easily added without modifying the code. The system calculates salary differentials for all months, taking into account delays in administrative orders for bonuses, salary increases, and promotions. It also has a responsive web design that adjusts the layout based on the visitor's screen size and orientation.
The first phases in system design are data collecting and analysis. Therefore, the design of WPMS started with gathering data and requirements from different environments. This procedure has been conducted using multiple styles and tools, as follows:
Quantitative method: For evaluating the system implementation, the system usability scale (SUS) has been used as a quantitative method. Furthermore, some of the essential data were obtained directly through observations.
Qualitative method: The SWOT (strengths, weaknesses, opportunities, and threats) technique is used as a qualitative method to determine the system requirements by a detailed analysis of the understudy university's internal and external environments. A questionnaire and an interview were utilized for this purpose. A questionnaire has been developed based on SWOT factors and reviewed by a group of strategic planning specialists and academicians. Resulting in accurate and direct access to the essential information.
There is some essential requirement that must be considered to design and build a reliable, efficient, and scalable system as functional requirements, which particularly define system functionalities, behavior, and goals, as follows:
There are several accounts for each faculty and each account does have a certain role. Each faculty has five different kinds of roles which are faculty admin, accountant, authorized viewer, auditor, and employee. However, there is only one main admin role for each university, which controls the accounts of all faculties. The system is designed to be a dynamic system that is intended to assist the main admin in the addition, updating, and deletion of faculties and their associated accounts on the system. The role of the main admin is the only role that can grant, delete, and change permissions of other users as well as access the aggregated salary reports of all faculties, whereas all other accounts may only see the salaries information of their faculty. Moreover, they are also limited to doing activities that are specifically assigned to them based on their role, such as the auditor is in charge of auditing; if an error exists, the auditor will not have the authority to modify it, while the accountant has the authority for inputting, removing, and modifying the data on payslips. Figure 2 shows the flowchart of the system accounts roles.
Regardless of a user's role, they all have their profiles, which include their name, faculty, email, etc. Additionally, all users may communicate with each other by sending and receiving messages, while the notification messages can only be sent by the main admin, which is either sent to all admins or employees. The processes of activities and responsibilities of all roles are shown in Fig. 3.
The operations (insert, update, and delete) of the data manipulation language (DML) are performed on three sections of the system. After each DML, a notification pops up to inform the user if the operation was successful or if an error occurred. These sections are as follows:
DML on payslips: The system can input, modify, and delete employees' salaries, as well as their deductions and allowances. During the execution process, there is a chance of interruptions and errors due to having many allowances and deductions for each employee. MySQL Transactions were utilized to overcome this issue, by grouping all data manipulation operations together sequentially. For a transaction to be completed, all operations within the group must be completed successfully. Otherwise, the whole transaction will fail.
DML on initial data: New users, deductions, allowances, and faculties may all be added, modified, and deleted at any time, making this a dynamic system, as shown in the employees' page in Fig. 4 and the allowances page in Fig. 5. There are some allowances all employees have, so when an employee's salary is added, these allowances are automatically added without the user having to select them.
c80f0f1006