Design Hints, Payroll System...repost

Question

Hi,

We are designing a Payroll Generation System for a client.

The organization we are targeting has a hierarchy as follows: Company -> Cluster -> Business Unit (BU) -> Department -> Employee

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.

These rules are editable and can be edited by a user end user. Also these rules are inherited top-down, but if defined at a lower level, the lower level rule takes precedence.

We have a database which has Attendance, Leaves, Bonuses tables and these rules are also supposed to interact with these tables.

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.

We are only looking to support SQL Server, and payroll generation will be an offline activity.

We are divided upon where to put the logic which uses these rules to generate the individual tax components (which will include Tax Deductions, Tax Write-offs, Allowances, etc).

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.

The order of our priorities are: 1. The ability to adapt changes to new clients quickly 2. Long term maintainability 3. Performance

1 and 2 outweigh 3 here by a large factor as this will be an offline activity.

Maintainability and Quick Customizability are very important, we will be deploying the application for different clients. Client A may have a Salary Component Rule as ((0.3 * Basic) + 800) and Client B as (0.2 * Basic) + (0.1 * Atendance Bonus)

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?

Suggestions and pointers to existing systems' architecture will be very helpful. ...and yes, we are using LINQ to SQL elsewhere in the system.

Kind regards, Ashish Sharma

Answer 1

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.

Answer 2

Maybe you want to check Agile Principles, Patterns, and Practices in C# The core example in the book is designing a payroll system the agile way....

Answer 3

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.

Why not combine the best of both? i.e. split the logic into separate components written as SPs, called from a master SP?

This is all data processing, why involve the "application layer" other than to call the master SP?

Answer 4

Depends how much decision points do you have in the algorithm. And how much change do you anticipate.

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#.

Answer 5

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.

You need to audit all the tables including recording who made the changes to data and when.

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.