Database Design Intermediate: Using Boolean Data in Mathematical Functions

If you are shy of maths you should continue reading. Most math-geeks don’t need to be told this stuff, so this section is not really for them, it’s for you. It describes two simple math tricks that are useful, so read on.

A common use of boolean values is in sales. Does sales tax apply? Is a discount voucher being used? In these situations the final price will be affected by the answers to these questions. The need to apply the data in a mathematical equation emphasises the importance of using number values for these questions.

Let’s look at the wrong way first. We have two fields, “applySalesTax” and “applyDiscount.” We’ll assume the worst, instead of using numbers the developer has used a single option value lists. One that says “Incl. Sales Tax” and the other says “Has Discount Voucher.” To evaluate the total price we need this calculation.

( 
Sale::subtotalOfGoods 
- if ( Sale::applyDiscount = "Has Discount Voucher" 
    ; Sale::discount 
    ; 0 
  ) 
) 
* 
if ( Sale::applySalesTax = "Incl. Sales Tax" 
    ; 1 + Sale::taxRate 
    ; 1 
    )

In this calculation we have the mathematical formula which is subtotal – discount + sales taxes. Within that formula we have two IF statements, one for each field giving us four branches in the logic. Each statement must be correctly formed to ensure that we end up on the right one of the four possible branches. And, of course, each of the four branches must be in the correct position too. All in all we have seven logical elements to maintain.

Now lets look at it using booleans. When we store the data to those questions as a number (boolean) the derivation becomes much easier. In our first example we subtract the discount and then apply sales tax. The entire statement is much simpler, there is no fork in the logic.

( Sale::subtotal - Sale::applyDiscount * Sale::discount ) 
* 
( 1 + Sale::taxRate * Sale::applySalesTax )

The boolean value is being used to full advantage. When “applyDiscount” is zero there is no discount. When “applyDiscount” is one the discount will be subtracted. Similarly, if “applySalesTax” is false (0) then the second part of the formula becomes ( 1 + 0) = 1. So the formula becomes X times 1, which is X. When “applySalesTax” is true (1) the taxRate is added to 1 and the subtotal is increased.

In our second example the discount is obtained by using the boolean “applyDiscount” with the “power” function to modify the discount rate (a percentage).

( Sale::subtotal * Sale::discountRate ^ Sale::applyDiscount ) 
* 
( 1 + Sale::taxRate * Sale::applySalesTax )

The power of zero and one are special cases. The value of a number X raised to the power of one is always X. The value of a number X raised to the power of zero is always one (1). This means that we can safely include the discount rate in our multiplication because when “applyDiscount” is zero, the value of discountRate ^ 0 is one, so the subtotal is not modified. When “applyDiscount” is true (1) the subtotal is multiplied by the discount rate and is reduced accordingly.

What are the benefits?

• These two methods can be used widely. They are simple and safe methods that have a broad range of applications.
• Neither of these examples require conditional logic functions.
• The same equation is valid for all sales regardless of whether they have discounts and/or sales taxes, so the code is more flexible.
• The code is simpler, so it is more likely to be correct and easier to correct when it is wrong.
• Neither example uses hard-coded text strings, which means that the code is more resilient.