CODEWITHSUNDEEP
sqloracle-databaseconstants
Andrew Martin
Andrew Martin

Reputation: 5741

Constants package best practice with heavily normalised database

I have a very normalised Oracle database. It has a number of large tables, along with maybe twenty or more very small tables (of < 10 records).

For example, there is a table of statuses. To give an idea of the data within, it looks something like this:

ID    Status
1     Cancelled
2     In Progress
3     Completed

Currently, throughout the code there are horrible magic number references to this, like so:

SELECT *
FROM   [somewhere]
WHERE  [something] = (
         SELECT Status
         FROM   [StatusTable]
         WHERE  Id = 2
       )

I hate these random numbers and would much prefer to replace them with constants.

My initial thought was a Constants package. There, I could have a global variable, 2, which returned the correct Status. It's in one place so problem solved - except of course it renders the table rather obsolete and isn't getting it's information from the table.

What is my best practice solution here? I do like the constants. I think they're a lot cleaner in the code, i.e.:

SELECT Status
FROM   [StatusTable]
WHERE  Id = CONSTANTS.Status_In_Progress

However, I am concerned that there is no attachment to the table data.

Upvotes: 2

Views: 184

Answers (2)

HLGEM
HLGEM

Reputation: 96590

If you want to change something, change to join to the table that contains the word value instead of just the int. Otherwise you have the problem of keeping your constants dat in synch with what the actual value is as these may change from time to time. More might get added, the text value might get changed. So you already have something to join to in a normalized database to get these values. Use it. At least then you don't have the additional problem of keeping Constants in synch.

However, even that is subject to change. And can cause more problems than using the magic number for a lookup value.

Suppose you have a status list in a table and the status for status ID 2 is 'InProgress' and someone later comes along and fixes it to 'In progress', do you want to change your code because the text got updated? Suppose you have a clientid? Company names change frequently. I have actually seen more problems from trying to use the verbiage than the magic number through the years when using lookups.

Now I grant you that magic numbers are not fun to deal with, but be wary of changing them and introducing new bugs especially if the text value of the lookup is subject to change (like a client name where "Sears, Inc' might become 'Sears and Roebuck, Inc.'). Lookups that are not likely to change such as state names can be referenced by their verbage but this will entail adding a join to the lookup table which you do not currently have in your queries as it stands. And more joins can start to add a bit of processing time to the queries. It could change the plan your database uses to find the data and then suddenly make the query unexpectedly slower. Adding a join you need is a good thing, adding one you may not could be the break point between a faster query and on that got mired (of course it may not add anything measurable either in most cases). Anytime you change code, you are introducing the possibility of a new bug or a change in performance.

Now you also have to consider the other devs who write code for this system. If everyone one knows that statusid 10 is 'complete', they may continue to use this type of code and you are fighting a losing battle. There is something to be said for using the standards that the organization already uses rather than making arbitrary changes to suit your preferences.

Upvotes: 1

Martina
Martina

Reputation: 929

From my several years experience in designing PL/SQL applications I can tell, there is no better way (mainly in terms of code readability and maintainance) than that you suggested, i.e. having constants like these stored in separate package spec and referring to them in queries and/or other processing.

Rather than as detattched from table data, I would think of it as sort of "interface" to data.

However let me make another point: You can start with this from now on in your new code or code rewritten due to change request, however to rewrite an existing, tested, in production running code just because "I don't like the way it looks like" is extremely weak reason,there is no added value in terms of app functionality however there is non-zero risk of importing bug..., I would rather go for another coffee or respond to new SO questions instead of such a work.

Upvotes: 0

Related Questions