It is very common for us to embed some kind of logic based on a lookup table or in some cases, a column of a table. Because we have application logic connected to it, the code are fragile and subject to changes as the requirements develops. We want to avoid this situation where we might find ourselves needing to change several places of code just to handle the changes. We need to accept that changes are inevitable and the requirements we are given on the first day may no longer be good enough tomorrow. Therefore we want an application that is tolerant of such changes in the requirement.
The problem becomes worse when we allow the users to modify the lookup table, which we commonly do via the admin center or equivalent. They will then expect their new changes to exhibit some application behavior and report it as a bug when their new status / type / whatever doesn’t work.
As discussed in ID vs Code, we want to avoid writing code similar to the following:
|
1 2 3 |
SELECT j.JobID, j.JobNumber FROM tblJobs AS j WHERE j.Closed <> 0 |
Or:
|
1 2 3 |
SELECT r.RequestID, r.RequestName FROM tblRequest AS r WHERE r.RequestTypeID IN (3, 5, 7); |
What’s wrong with those? Those seems perfectly harmless and probably do their jobs as per the original requirements. But in both cases, clients have come to us and asked for changes, so we end up changing to something like:
|
1 2 3 |
SELECT j.JobID, j.JobNumber FROM tblJobs AS j WHERE j.Closed <> 0 AND j.Pending <> 0 |
and:
|
1 2 3 |
SELECT r.RequestID, r.RequestName FROM tblRequest AS r WHERE r.RequestTypeID IN (3, 5, 7, 42, 93); |
In both cases, we tied several programming logic to both a column of a table in first example and to a ID in the 2nd example. In the first example, Closed is a bit field and thus the application had numerous queries or SQL code in VBA that would filter on Closed <> 0. In the 2nd example, RequestTypeID can be customized by the users via the admin center, and they added new types that requires same behavior as the 3 request types we programmed for.
How to avoid the quagmire?
There is no single fix-all cure but we can design our tables and therefore program our logic around this in a way that changes will not affect the existing implementation. The first thing to note is that if we’ve tied a programming logic to some status or some type, then this is not really something that an user can modify on their own because that necessarily requires programming of the application. Therefore, when we establish a behavior, it should be independent of other changes the users may be allowed to make to that table.
Therefore, we could modify the table structures for both examples:
|
1 2 3 4 5 6 |
CREATE TABLE tblJobs ( JobID int IDENTITY NOT NULL, JobNumber varchar(5) NOT NULL, ... JobStatusCode int ); |
Instead of tying a programming logic to one column and creating another column for a different logic, we can create a single single status column that is backed by a VBA Enum:
|
1 2 3 4 5 |
Public Enum JobStatus Closed = 1 Pending = 2 Open 3 End Enum |
Thus, we can now build our queries in VBA doing something similar to this:
|
1 2 3 4 5 6 |
Public Function GetSQLForJobStatus(JobStatusCode As JobStatus) As String SQLString = _ "SELECT * " & _ " FROM tblJob " & _ " WHERE JobStatusCode = " & PrepareSQLNumber(JobStatusCode) & ";" End Function |
By using this function consistently whenever we need to embed a query that filters on a job status, it does not requires us to change every place that is already filtering on an existing status; we can add a new status, introduce new programming logic and everything will continue to work as it has before.
Or for the 2nd example:
|
1 2 3 4 5 6 |
CREATE TABLE tblRequestType ( RequestTypeID int IDENTITY NOT NULL RequestTypeCode int, RequestType varchar(255) NOT NULL ... ); |
On the admin page where the user is allowed to edit the request types, we can expose RequestTypeCode as a limited & fixed combobox that only enumerates valid behaviors that we already have defined for the application. That way, when the user creates or modify the types, the user can select the behavior the user wants for this particular type, and the application will respond accordingly. If the user wants a new behavior, the user will have to contact us and we’ll have to program this new behavior but again, as long we consistently use the function to build the SQL based on a certain type behavior, the application will tolerate changes in the requirement better.