I have to design a database for the payroll system. The requirements are as follows:
- Admin can create payment items, such as Basic Salary, Overtime, Housing, Allowances at run time.
- Each payment item will have its own calculation formula.
- Some item's calculation formulas may depend on each other. For example, Housing is 20% of Basic.
- Each employee will have different sets of payroll items. For example an employee may have certain allowances that others don't have.
- For each employee, some items may be processed monthly (e.g. salary), yearly (housing), anytime during the year(bonus).
The problem is that I cant see consistency between the items to create a suitable design. For example, Overtime takes monthly inputs (number of hours worked each month) to perform the calculations, while basic salary input is fixed.
Does anyone have any suggestions about how I could design this?
Best Answer
You could try to build a fully table-driven rules engine, sometimes also called an inference engine, but you would be much better off buying one than trying to build your own, because they are very complex.
If the cost and/or complexity of a fully table-driven rules engine is too high for your organization, then you can build a compromise solution. The compromise involves mixing tables and procedural (code based) rules. The objective is to optimize the balance between flexibility and maintainability.
The way to achieve this is to carefully examine all of your current rules, taking into consideration as much as possible any potential rules that aren't used now, but which might come into effect in the future. This analysis should give you a set of rules for doing your payroll calculations.
You then need to break these rules down into two categories: (i) rules to be based in procedural code and (ii) rules to be table-driven. The best way to do this is to keep scalars in tables and more complex rules in code. For example the fact that one amount can be based on another amount is a good candidate for a code-based rule. The fact that the derived amount is 20% of the base amount is a good candidate for a table-driven rule.
For practical purposes, your rule tables will have a structure that includes the following:
Rule Type: This is a value that tells your procedural code which function/code-based rule uses the scalar data in a particular record.
Scalar Value: This is a numeric value that has a meaning which is understood by the procedural code indicated by the Rule Type.
Business Identifier: This is an indicator of which business element the rule applies to. Like the Scalar Value the Rule Type indicates which procedural code uses the rule record and that procedural code knows what to make of the Business Identifier. It might be a department number, an employee ID, a job code or whatever else your rules require to link payroll amounts to employees. Your rules table may need more than one Business Identifier, depending on how your rules work, or your requirements might dictate that business identifiers are normalized out into an intersection table.
Effective Date: One thing you can bet on is that your payroll scalars will change over time. Your system is going to need a record of what the rules used to be so you can make corrections to your payroll. Also, for the sake of the sanity of your HR and IT staff, you need to be able to record new rules before they go into effect.
Expiry Date: This completes the effective date range of the rule. I'd recommend using a max collating value so that every record has a fully qualified effective range. That will make your queries much simpler.
One of the realities of a solution like this is that you will have to come back to build new code-based rules from time to time as your organization's payroll policies change. Don't be discouraged by this. Even if you had a fully table-driven rules engine, changing payroll policies would entail making changes to the rules data. Business changes entail system changes - that's a fact of life.