Payroll System Design, Business Logic in SPs or Application Layer (C#.Net), Maintainability - 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?

The original post is here: http://stackoverflow.com/questions/208707/design-hints-payroll-systemrepost ...but none of the questions were properly answered.

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

If you store the formulas as in for example JEP (for java) it's not much of a problem. Just keep the full formula as a string: i.e: "pay=((0.3 * Basic) + 800) and then parse it into a tree.

You can see some of jep documentation for info, and take the ideas from there. Shouldn't be 2 problematic to implement simple solvers for the formulas you've posted here.

My sugestion:

• Keep it in a string, in the data base
• Make a small library for the eval and parser.
• Parse it into a binary tree. (like '+' points to 800 and also points to '') then the '' points to 'basic' and to '0.3'
• After it you just need a simple recursive function to solve it.

If the complexity of these formulas aren't much you can do this in any side you want as it won't take you that much time to process it.

Answer 2

I always try to shy away from putting business logic in the DB layer. It is more difficult to write, debug and maintain. In addition, the DB is generally the most expensive layer to scale. If you end up needing to beef up your system to support more users it is relatively cheap and easy to add new webservers to a system, but adding DB instances becomes expensive as each DB requires a license and additional support.

Answer 3

If you are looking for a .Net solution that will evaluate formula represented in strings, here an is excellent article that details using ANTLR and C# to create a calculation engine. There is a fully functional formula interpreter that parses strings, builds an AST, then evaluates the expression. In addition, the calculation engine is implemented using the Visitor pattern, so if you have functions that you wish to perform such as database lookups you can easily incorporate the customization into your solution.

Answer 4

Have you heard of Relection or delegates ?