Recently, I’ve noticed that some developers, while fully understanding what a lookup table is for in terms of normalising data, miss opportunities to use them in additional ways. This post is therefore about those further uses for lookup tables that will really give you an opportunity to streamline your code.
For the purposes of this discussion, I want to use an example based on a table I have worked with in the workplace. There is a Payment table, and it refers to a PaymentType table to indicate two things:
- The Type of Payment, if it’s a normal payment, or a charge, or whatever
- The status of the payment, which is to say, has it been paid or not
Now, in general, I’d prefer to see this as two separate columns in our Payment table; a ‘Payment Type’ lookup, and a boolean (probably) to indicate if the payment had actually been received. Nevertheless, this is a real-world problem, and sometimes we have to take what we find! So here’s the lookup table as it stands:
PaymentTypeId Description 0 Potential 1 Unpaid Payment 2 Paid Payment 3 Defaulted Payment 4 Unpaid Charge 5 Paid Charge 6 Defaulted Charge
…you get the idea. By the way, in terms of the system this table belongs to, a ‘Potential’ payment is one that we are not sure that we should take; basically the payment is attached to a loan that has not been finalised yet, so we don’t want our payment processors and so on to take this payment.
Now, unfortunately, the code and SQL that uses this table has also not benefited from refactoring over time, so we tend to see a lot of code or SQL that has to refer to these values specifically. For example, say we need to know which payments we should try and take today:
SELECT * FROM payment p JOIN loan l ON p.loanId = l.loanID JOIN paymentType pt ON pt.paymentTypeId = p.paymentTypeId WHERE pt.Description in ('Unpaid Payment', 'Defaulted Payment', 'Unpaid Charge', 'Defaulted Charge') AND p.DateDue = ... etc ...
Now, as new types of payment are added, or reverse payments (e.g. ‘Chargebacks’ from a merchant-services provider or card-company) we risk that areas of the code, or sql or reporting will be neglected, and that could result in errors further down the line… because one particular bit of SQL was not changed, but it still appeared to be working so no-one noticed.
Now, how about adding a column to our PaymentType table to indicate that our payment processors should attempt to take this payment record? Our table would look like this:
PaymentTypeId Description TakePayment 0 Potential false 1 Unpaid Payment true 2 Paid Payment false 3 Defaulted Payment true 4 Unpaid Charge true 5 Paid Charge false 6 Defaulted Charge true
Now, we can change our SQL to this:
SELECT * FROM payment p JOIN loan l ON p.loanId = l.loanID JOIN paymentType pt ON pt.paymentTypeId = p.paymentTypeId WHERE pt.TakePayment = 1 -- In SQL Server, bit fields are referred to by 1 and 0 for true / false AND p.DateDue = ... etc ...
By implementing this additional value on the lookup table, we have brought a requirement of the rest of the system to the surface; that we need to try and take some payment-types, but not attempt others!
The wonderful thing with this approach is that – as long as the purpose of our additional column is clear – we can add new records to the PaymentType table, and know that by setting certain values in specific ways, we can impact many SQL commands and code statements in ways that are logical (we hope).
There are risks with this approach, of course. If you have low-skilled users who have write-access to the database, then there is the risk that they will change these values and thus affect the whole operation of the system. Nevertheless, this risk exists if you have any human with write access to the database! For example, if you do not have a foreign-key relationship set up on the database, and innocuous mistype in a SQL command can cause serious issues:
DELETE FROM PaymentType; WHERE paymentTypeId = 99
Oops. You just deleted all the rows in the table because of that misplaced semi-colon on the first line (which caused the WHERE condition to be ignored)!
Essentially, if you find yourself writing code or SQL that specifically has to specify some particular Id value(s) or lookup-Names, consider having extra data on the lookup to identify this… for example, this particular PaymentType table could benefit from a column to indicate if the PaymentType is a credit or debit to the overall account – which again could be represented with a simple bit / boolean field.
As ever, many of the issues I have highlighted could and will be addressed in environments with good procedures, centralised locations of SQL, and so forth. However, I am confident that even in such a well-organised environment, adding meta-data to your lookup tables can simplify your code now and in the future!