Updated: 9/3/2008

My approach is highly table-driven to make it flexible and non-programmer-configurable. In some ways it actually implements a domain-specific spreadsheet-like contraption because calculation formulas are also stored in tables. However, even if the formulas are moved to app code instead of tables (example given later), its table-driven nature is only slightly dampened.

The scope of the application here is generally assumed to be a payroll system to be sold or rented to multiple small or medium companies. The software company has Account Representatives that assist each customer with installation, site-specific configuration, and technical questions. (A given representative may assist multiple clients.) If you disagree with this stated scope, or disagree with Robert Martin's implementation, you are welcome to present your own.

Features not Implemented

• Front ends (edit screens) to edit employee and payroll specification information.
• Validation of feature combinations and quantities.
• Flushing out and archiving old information after a pay cycle is finished.
• Front end to manage and launch pay cycles.
• Actual check printing and distribution. Only stubs are printed here.

Advantages over Robert Martin's OOP Approach

He also seems to obsess on being able to swap database vendors without significantly changing the code. He spends significant coding effort up-front to prevent this alleged swapping catastrophe. In my opinion, he is paying way too much for his meteor insurance. I highly question his cost/benefit analysis on this decision. (Vendor swapping "protection" often results in long heated debates and covering it here in detail could fill up several pages.)

Here is a general grouping of the advantages I see in my approach over Martin's:

1. Flexibility - R. Martin hard-wired concepts into his design that are table-configurable in this one. Such concepts include employee pay "types" (hourly, commission, exempt, etc.), union dues, sales commissions, etc. Some OO proponents will suggest that hard-wiring in such concepts allows the compiler to find errors, such as feature combinations that are not allowed to exist. But generally a "feature combination checker" would be set up and is needed for other purposes anyhow. (More on this later.)

One generally wants domain administrators to configure domain product and feature information, not programmers. There is a popular book on the market that does the same with a Starbucks-like coffee shop, so Martin is not alone taking this approach. Too many OO examples turn programmers into glorified product clerks.

Some will suggest that for custom small single-shop software, hard-wiring in such concepts may be simpler than trying to make them table-configurable. To some extent I tend to agree. However, OO proponents often claim that OO is for large-scale projects and that it reduces the amount of code that needs changing per change request. Hard-wiring flies in the face of both of these goals. As far as scaling, if you have mass volumes of features or products, you generally need a database to keep track of them all. If a few very gifted individual developers can speed-read app code for product/feature classification info, I admire such a trait, but they can't assume all developers are gifted at such speed-reading.

Plus, managing product and feature classifications should generally be done by domain experts (product or account administrators), not programmers, because domain experts are closer to and better understand the day-to-day business decisions. The developer's job is to provide a framework for the domain experts to manage most of such info themselves. Note that for our assumed scope, it may be Customer Representative that enters formulas and not necessarily the clients themselves. In practice it may depend on the skill-level of the client. Some clients/customers need more hand-holding than others.

Further, it is hard to report on and query such features if they are only in app code. People usually want to study such info from different perspectives and thus need queries and reports. It would not be unexpected for a manager to ask, "How many employees who have 401K retirement plans also pay union dues? And, can I have a bar chart of the wage distribution in 5k increments?"

Fans of dynamic OOP languages, such as SmallTalk and Python, generally do not support "compile the world up-front" validation claims anyhow, or at least feel that benefits of dynamic-ness outweighs the downsides. (In some ways, dynamic OOP solutions often resemble this table-driven one. However, they will use navigational structures instead of tables. The relational-versus-navigational debate is an age-old one that I will not repeat here.)

2. Simplicity - The approach shown here is less code even though it does more than Martin's, such as displaying actual stubs and allowing user-configurable line-items. And, it shows the database interface code (SQL), something Martin excluded because he views RDBMS as low-level tools to be hidden away. (In some ways, RDBMS are a higher-level tool than OOP code.)

3. Integration-Friendly - By using tables, user/customer sites can interface with the system easier. For example, sales-commission-related information can be populated into the EmpCharges table without having to know anything about the application programming language: they "talk to" tables, not OOP classes. Such systems or utilities are sometimes referred to as "feeder systems"; and are a common need in real-world business apps.

In this case, the customer's sales tracking system would generate commission information which could be loaded periodically into the "EmpCharges" table using the "sales_com" pay item code. Keep in mind that "sales_com" is not hard-wired into this application either. It is merely an item convention established for this example. (Paying employee sales commissions is part of Martin's requirements. However, he seemed to want them on a separate stub. Splitting them is relatively trivial, so it is not shown here.)

Language

The example uses a mix of syntax styles provided by ColdFusion: one in its HTML/XML-like tag-based mark-up language, and another in it's JavaScript-like style. The tag-based language offers more features; however, I used the JavaScript-like version for some parts to make it more familiar to readers.

The tag-based language is generally easy to read once you get a feel for its conventions. Each tag name starts with "cf" such that an "if" statement tag looks like "<cfif [condition]>".  If you can figure out the similarities in the following example listings, you should have no trouble reading the CF tag language:

  // JavaScript equivalent for syntax comparison
  x = doFoo(7, 99);
  function doFoo(p1, p2) {
      var result="";
      if (p1 > p2) {   // see "GT" note
          result="greater than";
      } else {
          result="less than or equal";
      }
      miscFunc(result, p1, p2);
      return(result);
  }

  <!--- ColdFusion markup language version --->
  <cfset x=doFoo(7, 99)>
  <cffunction name="doFoo">
      <cfargument name="p1">
      <cfargument name="p2">
      <cfset var result="">
      <cfif p1 GT p2>
          <cfset result="greater than">
      <cfelse>
          <cfset result="less than or equal">
      </cfif>
      <cfset miscFunc(result, p1, p2)>
      <cfreturn result>
  </cffunction>

Algorithm Description

The EmpCharges table has an optional parameter value ("ParamValue" column). This is used to supply any needed amounts, quantities, percentages, etc. to the process as needed. If the PayItems table has a corresponding formula, then that formula is used to calculate the line-item. If there is no formula, then the parameter value becomes the value seen on the paycheck.

The "PayItems.SumMult" column tells it how to sum up items in a paycheck. It is multiplied by the line-item's calculation result to determine whether it is added or subtracted from the pay. A zero value means that it is only a display-only and/or reference value. The SumMult values are used as follows:

• 1 (one) - Add value to paycheck total
• -1 (negative one) - A deduction: subtract value from paycheck total
• 0 (zero) - Ignore value for total amount (it may still show as a reference value)

Here is some general pseudo-code for the calculation of a given line-item:

  // pseudo-code for calculations
  if there is a formula then
     result = evaluate(formula)
     // formula may or may not use parameter_value
  else
     result = parameter_value
  end-if
  result = sumMult * result   // add, subtract, or ignore

The data for Mr. Jones (Employee 102) shows an example of a line item occuring multiple times. Unlike the other employees, Mr. Jones' paystub is based on hours tallied for individual days instead of a weekly or period-based sum. Let's examine the formula for the "reg_hours" item:

    paramVal() + itemSum("daily_reg_hrs")

A validation step, not provided here, should probably be applied to make sure such suspicious combinations and quantities don't occur. Note how such a tool could be written in a completely different language than our app language here, because it could use just the table data to analyze the payment specifications.

Another notable column is "EmpCharges.Reoccurring". If its set to true (one), then it means that the charge stays for each cycle, until explicitly removed. False (zero) means that the item is cleared out after each cycle. The parking fee is typical of a reoccurring charge, and a sales commission or bonus is an example of a non-reoccurring charge.(The removal system is not included here.)

Static Formulas

  // static formula example pseudo-code
  function calculate(payItemID, defaultVal) {
    var result = defaultVal;
    case payItemID {
      when "reg_hrs": {result = paramVal() + itemSum("daily_reg_hrs");}
      when "payroll_tax": {result = itemSum("raw_pay") * paramVal();}
      when "sales_tax": {result = itemSum("sales_com") * 0.12;}
      // etc....     
    } // end-case
    return(result);
  }

Note that we don't have to include a formula for every line-item, only those with formulas. OO proponents often complain about "duplicate" case statements that polymorphism alleged fixes (I often disagree with their duplication scoring criteria), but we have no need for such here. It occurs only once.

Another variation would be to supply a graphical tree-based "equation editor" with the product. This would greatly reduce the chances of problem formulas. I've seen examples in other software and the idea seems promising.

Other Program Features and Ideas

Employees are assigned to a particular cycle ("Employees.EmpPayPeriod"). The PayDates table then maps the cycles to dates. Note that it is possible for more than one cycle to fall on the same date (not shown here).

Near the top of the listing you will see this function call:

  generatePayroll("PD.payDate = '2000-01-01'")

A reader suggested year-to-date (YTD) and since-hire accumulator columns for the EmpCharges table, for these are commonly used in payroll calculations.

If we wanted longer and more complicated calculations inside our app instead of farmed off to a side application (which is a perfectly good choice for some calculations), then we could make PayItemId be non-unique in the PayItems table, perhaps adding another table, something like "PayItemGroup" to supply a general description to the grouping, and make two "layers" of order sequences. However, this would complicate the application. Example schema:

  table: PayItemGroups
  --------------
  PayItemGroupId
  GroupTitle
  GroupSequence   

  table: PayItems
  ---------------
  PayItemGroupRef  // Used to be "PayItemId". F.K. to above
  CalcSequence
  ...etc...

Pay Stub Samples

Name:	Jones, Robert M.
Empl. ID:	102
Payroll Date:	2000-01-01
Item Description		Reference Value	Pay Amount
Hourly Rate		27
Daily Regular Hours Sep. 04		8
Daily Regular Hours Sep. 05		8
Daily Regular Hours Sep. 06		8
Daily Regular Hours Sep. 07		8
Daily Regular Hours Sep. 08		4
Regular Hours		36
Raw Pay			972.00
Total			972.00

Name:	Johnson, Mark V.
Empl. ID:	103
Payroll Date:	2000-01-01
Item Description		Reference Value	Pay Amount
Hourly Rate		25
Regular Hours		32
Raw Pay			800.00
Payroll Tax			-156.00
Sales Commission - Reader's Digest (qty 1)			8.50
Sales Commission - Time (qty 2)			5.00
Commission Sales Tax (12%)			-1.62
Total			655.88

Table Descriptions and Samples

 Table: PayItems

PayItemId	CalcSequence	ItemDescript	SumMult	Formula	DisplayOptions	PI_Notes
hourly_rate	30	Hourly Rate	0
daily_reg_hrs	40	Daily Regular Hours	0
reg_hrs	50	Regular Hours	0	paramVal() + itemSum("daily_reg_hrs")		Formula takes both detail or sum
ot_hrs	70	Overtime Hours	0
raw_pay	90	Raw Pay	1	itemSum("hourly_rate") * itemSum("reg_hrs") + itemSum("hourly_rate") * 1.5 * itemSum("ot_hrs")
payroll_tax	200	Payroll Tax	-1	itemSum("raw_pay") * paramVal()
401K	300	401K	-1	itemSum("raw_pay") * paramVal()
union_dues	400	Union Dues	-1
union_fee	500	Union Fee	-1
union_taxes	600	Union Taxes (7.5%)	-1	(itemSum("union_dues") + itemSum("union_fee")) * 0.075	(supprzero)
parking_fee	700	Parking Fee	-1	30.00
sales_com	800	Sales Commission	1
sales_tax	900	Commission Sales Tax (12%)	-1	itemSum("sales_com") * 0.12	(supprzero)

Table "PayItems" columns:

• PayItemID - Text-based ID for a pay-item
• CalcSequence - Sequence ordering of calculation (type: double)
• ItemDescript - Description of pay item
• SumMult - How added to pay total. -1 = subtract from total, 0 = no change, 1 = add. Value is rounded to currency.
• Formula - Formula used in line-item calculation
• DisplayOptions - Display option codes. Surround each with parentheses. In a more formal system, these would be assigned via a many-to-many table.
• PI_Notes - Internal notes

 Table: Employees

EmpID	EmpPayPeriod	FirstName	LastName	Middle	CheckDeliveryMethod
101	weekly	Lisa	Smith	K.
102	weekly	Robert	Jones	M.	mail
103	weekly	Mark	Johnson	V.
104	monthly	Tori	Lee	J.

 Table: EmpCharges

EmpRef	PayItemRef	ParamValue	AddtnlDescript	Reoccurring	ChargeID
101	401K	0.03		0	1
101	hourly_rate	30		1	16
101	ot_hrs	2		0	3
101	parking_fee	0		0	4
101	payroll_tax	0.21475		1	5
101	raw_pay	0		1	26
101	reg_hrs	40		0	2
101	sales_com	12.5	- QC magazine (qty 3)	0	9
101	sales_tax	0		1	10
101	union_dues	10		1	6
101	union_fee	15		0	7
101	union_taxes	0		1	8
102	daily_reg_hrs	8	Sep. 06	0	22
102	daily_reg_hrs	8	Sep. 05	0	21
102	daily_reg_hrs	8	Sep. 07	0	23
102	daily_reg_hrs	4	Sep. 08	0	24
102	daily_reg_hrs	8	Sep. 04	0	20
102	hourly_rate	27		1	25
102	raw_pay	0		1	27
102	reg_hrs	0		1	32
102	sales_tax	0		1	34
103	hourly_rate	25		1	11
103	payroll_tax	0.195		0	14
103	raw_pay	0		1	28
103	reg_hrs	32		0	18
103	sales_com	8.5	- Reader's Digest (qty 1)	0	12
103	sales_com	5	- Time (qty 2)	0	13
103	sales_tax	0		1	15
104	hourly_rate	28		1	30
104	raw_pay	0		1	31
104	sales_tax	0		1	33

Table "EmployeeCharges" columns:

• EmpRef - Foreign key to Employee table
• PayItemRef - Foreign key to PayItems table
• ParamValue - Value used for item value, or for formula if "paramValue" function used. See documentation
• AddtnlDescript - An extra description that is appended to "itemDescript" from PayItems table when generating paycheck
• Reoccurring - A Boolean flag that indicates wether this is a reoccuring charge. If not reoccurring, then it is cleaned out after payroll is processed.
• ChargeID - A sequentially-assigned number (plays only a minor role in this example)

 Table: PayDates

PayDate	PayPeriod
2000-01-01	weekly
2000-01-02	monthly
2000-01-08	weekly

Table PayStubs (contents generated by process):

("TraceInfo" is for internal inspection only. It is to assist with diagnosing any calculation problems or confusion.)

Code Listing

<!--- ********************** --->
<!--- Payroll Example        --->
<!--- Language: ColdFusion 7 --->
<!--- ********************** --->

<cfprocessingdirective suppresswhitespace="true">
<cfsetting showdebugoutput="false">

<cfset dsn = "test_sample">   <!--- default data-source name --->

<body bgcolor="white">

<!--- Initialize module-level variables --->
<cfset useValue = 0>   
<cfset groupSums = structNew()>       <!--- to store group sums --->

<!--- main processes (test plug) --->

<cfquery name="clear_stubs" datasource="#dsn#">
    DELETE FROM paystubs
</cfquery>

<cfset generatePayroll("PD.payDate='2000-01-01'")>  

<cfset printStubs()>

</cfprocessingdirective>

<!--- --------------------------------- --->
<cffunction name="generatePayroll">
    <cfargument name="criteria">
    
    <cfquery name="empsQry" datasource="#dsn#">
        SELECT DISTINCT  empID, payDate
        FROM PayDates PD, Employees EM
        WHERE PD.payPeriod = EM.empPayPeriod
            AND    (#preserveSingleQuotes(criteria)#)    
        ORDER BY empID
    </cfquery>

    <cfloop query="empsQry">
        <cfset payEmployee(empsQry.empID, empsQry.payDate)>
    </cfloop>
</cffunction>    
<!--- --------------------------------- --->
<cffunction name="payEmployee">
    <cfargument name="target_empID">    
    <cfargument name="target_payDate">        
    
    <cfquery name="payQry" datasource="#dsn#">
        SELECT *
        FROM EmpCharges EC, PayItems PI
        WHERE EC.payItemRef = PI.payItemID
            AND EC.empRef = #target_empID#
        ORDER BY PI.calcSequence, EC.chargeID, EC.chargeID
    </cfquery>
    <!--- <cfdump var="#payQry#"> --->    

    <cfset structClear(groupSums)>      <!--- re-init group sums --->
    
    <cfloop query="payQry">
        <cfset traceInfo = "">    <!--- initialize for this item --->
        <cfset payValue = 0>
        
        <!---  primary calculations --->
        <cfset useFormula = payQry.formula>
        <cfset useValue = numClean(payQry.paramValue)>
        <cfset traceInfo = traceInfo & " param: #payQry.paramValue#">
        <cfif Not isBlank(useFormula)>
            <cftry>   <!--- execute formula --->
                <cfset useValue = evaluate(useFormula)>
                <cfcatch>  
                    <cfabort showerror="ERROR IN FORMULA : #useFormula# : 
                        #cfcatch.message# #cfcatch.detail# : #cfcatch.type#">
                </cfcatch>
            </cftry>
            <cfset traceInfo = traceInfo & " (formula used)">
        </cfif>
        
        <!--- if part of paycheck total, prepare a monetary value --->
        <cfset skipSumFlag = (numClean(payQry.sumMult) is 0)>  
        <cfif Not skipSumFlag>
            <cfset payValue = payQry.sumMult * round(useValue * 100) / 100>  
            <cfset traceInfo = traceInfo & " Amount * " & payQry.sumMult>
        </cfif>

        <!--- Save to Stub --->
        <cfset insertStubLine(target_empID, target_payDate, payQry.payItemID, payQry.currentRow,
            trim(payQry.itemDescript & " " & payQry.addtnlDescript), useValue, payValue, traceInfo)>

        <!--- Add to group sums --->
        <cfset structAdd(groupSums, payQry.payItemId, useValue)>
    </cfloop>

</cffunction>
<!--- --------------------------------- --->
<cffunction name="insertStubLine">
    <cfargument name="p_empID">
    <cfargument name="p_payDate">
    <cfargument name="p_payItemRef">
    <cfargument name="p_lineNum">
    <cfargument name="p_lineDescript">
    <cfargument name="p_referenceAmt">    
    <cfargument name="p_amount">
    <cfargument name="p_traceInfo" default="">
    <cfif false>  <!--- for debugging only --->
        <cfoutput>(stubline: emp=#p_empID#, it=<b>#p_payItemRef#</b>, ln=#p_lineNum#, dt=#p_payDate#, 
            refAmt=#p_referenceAmt#,amt=#p_amount#, "#p_lineDescript#", trace=#p_traceInfo#)
        </cfoutput>
    </cfif>
    <cfquery name="insertStub" datasource="#dsn#">
        INSERT INTO payStubs (empRef,payDate,lineNum,payItemRef,
            lineDescript,referenceAmt,amount,traceInfo) 
        VALUES (#p_empID#,'#p_payDate#',#p_lineNum#,'#p_payItemRef#',
            '#p_lineDescript#',#p_referenceAmt#,#p_amount#,'#trim(p_traceInfo)#')
    </cfquery>
</cffunction>
<!--- --------------------------------- --->
<cffunction name="printStubs">
    <cfquery name="stubQry" datasource="#dsn#">
        SELECT * 
        FROM payStubs, employees, payItems
        WHERE empRef = empID AND payItemRef = payItemID
        ORDER BY payDate, empRef, lineNum 
    </cfquery>
    <!--- <cfdump var="#stubQry#"> [for debugging] --->
    <h3>Pay Stub Samples</h3>
    <cfset shadeClr = "##f0f0f0">
    <cfoutput query="stubQry" group="empID">  <!--- outer group by empID --->
        <cfset sumPay = 0>    <!--- init --->
        <hr>
        <table border=1 cellpadding=2 cellspacing=0>
            <tr>
                <td bgcolor="#shadeClr#" width="25%">Name:</td>
                <td colspan=3>#stubQry.lastName#, #stubQry.firstName# #stubQry.middle#</td>
            </tr>
            <tr>
                <td bgcolor="#shadeClr#">Empl. ID:</td>
                <td colspan=3>#stubQry.empID#</td>
            </tr>
            <tr>
                <td  bgcolor="#shadeClr#">Payroll Date:</td>
                <td colspan=3>#stubQry.payDate#</td>
            </tr>
            <!--- column headings --->
            <tr bgcolor="#shadeClr#">
                <th colspan=2>Item<br>Description</th>
                <th>Reference<br>Value</th>
                <th>Pay<br>Amount</th>
            </tr>
            <!--- for each line item --->
            <cfoutput>   
                <!--- skip line if zero-amount suppression is on --->
                <cfif Not (stubQry.displayOptions Contains '(supprzero)' 
                    And stubQry.amount EQ 0.00)>
                    <tr>
                        <td colspan=2>#stubQry.lineDescript#</td>
                        <td align="right">
                            <cfif stubQry.sumMult is 0>
                                #stubQry.referenceAmt#
                            <cfelse>
                                &nbsp;
                            </cfif>
                        </td>
                        <td align="right">
                            <cfif stubQry.sumMult NEQ 0>
                                #numberFormat(stubQry.amount,"-9,999,999,999.99")#
                            <cfelse>
                                &nbsp;
                            </cfif>
                        </td>
                    </tr>
                </cfif>
                <cfset sumPay = sumPay + stubQry.amount>
            </cfoutput>
            
            <!--- Total Line --->
            <tr>
                <td colspan=3 align="right"><b>Total</b></td>
                <td align="right">#numberFormat(sumPay,"-9,999,999,999.99")#</td>
            </tr>
        </table>
    </cfoutput>
</cffunction>
<!--- --------------------------------- --->
<cfscript>
function paramVal() {   // used in formulas to return parameter value
    return(useValue);
}
//------------------------------------------
function itemSum(item_name) {  
    // used in formulas to return summation of given group, zero if not found
    return (structGetValue(groupSums, item_name));
}
//------------------------------------------
function structAdd(theStruct, theKey, theValue) {
    // add value to given structure, initialize with value if new key
    theKey = trim(theKey);
    if (structKeyExists(theStruct, theKey)) {
        theStruct[theKey] = theStruct[theKey] + theValue; 
    } else {
        theStruct[theKey] = theValue;   
    }
}
//------------------------------------------
function structGetValue(theStruct, theKey) {
    // Get value from structure, return zero if key not found
    theKey = trim(theKey);    
    if (Not len(theKey)) {
        return 0;
    }
    if (structKeyExists(theStruct, theKey)) {
        return theStruct[theKey];
    } else {
        return 0;
    }
}
//------------------------------------------
function numClean(theValue) {   
// Ensure that a number is non-null (use zero) and valid. See notes.
    var result = trim(theValue);
    if (Not len(result)) {
        result = 0;
    }
    if (Not isNumeric(result)) {
        errorThrow("Invalid Number: " & result);
    }
    return(result);
}
//------------------------------------------
function isBlank(theValue) {   // returns True if blank after trimming
    return(len(trim(theValue)) is 0);
}
</cfscript>
<!--- --------------------------------- --->
<cffunction name="errorThrow" output="true">
    <cfargument name="theMessage">
    <h2> ERROR: #theMessage#</H2>
    <cfabort showerror="#theMessage#">
</cffunction>
<!--- --------------------------------- --->