Some Refactoring Solutions For Magic Numbers
Today I’m looking at solutions to Magic Numbers. Magic numbers are hard coded numbers (or references) which appear in scripts and procedures, they creep in very easily.
I don’t think that magic numbers are entirely bad. If you have a stored procedure which modifies data for a specific customer, having the one customer ID in that one procedure isn’t going to hurt anything. I know there is a line between best practise, and the real world of small implementations.
When smaller implementations grow, that’s when magic numbers are best dealt with quickly and properly, before they are found everywhere.
When magic isn’t magical
Magic Numbers do become a problem if they have a business meaning associated with them. Such as:
- Customer(s) to bill differently.
- Accounts to include in a specific report.
If these references are only stored in scripts/procedures and get used in more than one place, problems can develop quickly. Each time a customer gets added to the list of customers to bill differently, their ID needs to be added to every process which applies that logic. I have known cases where there are multiple lists in different processes of customers which attempt to apply the same logic, and the list of IDs wasn’t consistent across the whole script (this was the cause of the issue being debugged).
Example: Applying a discount
This example is of a report, with some customers who get a discount and only pay a percentage of the invoice charge. I’m aware that this is technically the wrong way to do everything, but I wanted to keep my examples simple.
/*
** Apply discount to invoice
*/
SELECT c.customer_id,
c.customer_name,
i.invoice_id,
i.invoice_amount,
CASE c.customer_id
WHEN 2 THEN 0.90
WHEN 3 THEN 0.80
ELSE 1
END * i.invoice_amount AS billed_amount
FROM customers c
INNER JOIN customer_invoices i
ON i.invoice_customer_id = c.customer_id
;
Lines 9 – 13 show the discounts being applied. It’s very easy to see how users writing reports can just keep extending this list to include more IDs and rates.
Many instances where magic numbers become problematic is where the work to refactor is greater than the effort of adding another number to the list.
Example: Selectively applying a tax
This example is for the UK, where we have a 20% VAT (Value Added Tax) which is charged on products and services. I’m keeping this example simple and assuming that the rate doesn’t change and that it’s applied uniformly. At some point in time, our organisation starts trading outside of the UK and gets one, then another (and more in the future if business is good) customers who aren’t charged VAT.
/*
** Customers not charged VAT (UK Tax)
*/
SELECT c.customer_id,
c.customer_name,
i.invoice_id,
i.invoice_amount,
CASE WHEN c.customer_id IN (3, 5)
THEN 0
ELSE .2
END * i.invoice_amount AS VAT_amount
FROM customers c
INNER JOIN customer_invoices i
ON i.invoice_customer_id = c.customer_id
;
Lines 8 – 11 show where logic has been written to calculate 20% of the invoice value for VAT, but with two customers (3 and 5) who aren’t charged this tax.
But when people start working with lists in SQL Scripts and procedures, it has to stop, and stop quickly before they become unmaintainable.
Refactoring to solve the problem
The best way to solve magic numbers is to refactor and store the information in a table. Using only minor code modifications, the DBMS can work with the references quickly and easily. With the extra benefits of:
- The business meaning and values are stored in the database.
- An update in one place automatically updates every process which uses that reference.
Solution 1: Adding extra attributes
If the customer table is extended to store an extra value for each magic number, the code could look like the below:
/*
** Apply discount to invoice in code
*/
SELECT c.customer_id,
c.customer_name,
i.invoice_id,
i.invoice_amount,
c.customer_invoice_rate * i.invoice_amount AS billed_amount
FROM customers c
INNER JOIN customer_invoices i
ON i.invoice_customer_id = c.customer_id
;
/*
** Customers not charged VAT (UK Tax)
*/
SELECT c.customer_id,
c.customer_name,
i.invoice_id,
i.invoice_amount,
c.customer_vat_rate * i.invoice_amount AS VAT_amount
FROM customers c
INNER JOIN customer_invoices i
ON i.invoice_customer_id = c.customer_id
;
Looking at lines 9 & 22, the rates are now stored in a table and managed outside of the script.
Solution 2: storing a list of references
This is a variation on solution 1, it’s based on scenarios where:
- The customer table can’t be modified (well, not by you).
- The list of IDs is only used in this one process (it could be an ad-hoc report), but is referenced multiple times in the process.
Rather than modifying the core table, a new table is created to store the values.
/*
** Apply discount to invoice
*/
CREATE TABLE customer_discounts
(
customer_id INT NOT NULL,
customer_invoice_rate DECIMAL(3,2),
CONSTRAINT PK_customer_discounts PRIMARY KEY (customer_id)
);
INSERT
INTO customer_discounts
(customer_id, customer_invoice_rate)
VALUES( 2, 0.90),
( 3, 0.80)
;
This table is then referenced (using COALESCE to get a value for everyone not in it) to get the values and apply them:
/*
** Apply discount to invoice in code
*/
SELECT c.customer_id,
c.customer_name,
i.invoice_id,
i.invoice_amount,
COALESCE(d.customer_invoice_rate, 1) * i.invoice_amount AS billed_amount
FROM customers c
INNER JOIN customer_invoices i
ON i.invoice_customer_id = c.customer_id
LEFT JOIN customer_discounts d
ON d.customer_id = c.customer_id
;
Working Examples
See the problem & solution for magic numbers in the below examples:
In the below examples, the SQL is mostly portable (I tested with MySQL and SQL Server), for Oracle the inserts need to be modified.