|"Smart Coding"||KEVIN RESCHENBERG|
You've seen it—the department ID field in which the first character
designates the region, the next two characters indicate the division,
the next three identify the city, and so on. There are various names
for this, but I'll call it "smart coding"—the practice of building
meaning into an identifying code.
Smart coding is not very smart in most cases. As developers, we should
discourage the practice. There are several problems with smart codes.
First, whenever an attribute of the thing (department, product, etc.)
changes, the code must be changed. Since this code is the key to a
table and is contained in many other tables, we must then do a special
update to all of the affected tables. (As mentioned in my recent post
integrity, a PeopleSoft database does not enforce these key
relationships and it is therefore our responsibility to ensure that
all of the relevant tables are updated.) If we instead decide to keep
the original code, we then must remember that the first character of
the department ID does not always indicate the region. This
can lead to ridiculous program logic (the region is the first character,
except for department 1482312, where the region is really 3, and
department 2889102, where...).
Another problem is that smart coding defeats one of the main purposes
of a relational database—to allow joins between tables based on
individual attributes. We should have a region table, but we would
then need to join to it using a substring of the department ID. In
addition, we cannot take advantage of the usual PeopleSoft page
mechanisms such as prompt tables and related display fields without
Another issue is the "running out of values" problem. Suppose that
your company has a division that grows apples and another that grows
bananas. You might create division IDs of "A" and "B," and that would
seem reasonable. Then a new division is created to grow beans.
We can't use "B" so we'll call it "C." But then what do we use for
the new Cherries Division? The HRMS
system has a field for employment status. It is convenient to know that
"A" means "active" and "T" means "terminated." But there are so many
values that we also have "Q" for "retired with pay," "V" for "terminated
pension payout," and so on.
I once had a situation in which a little smart coding was for all
practical purposes required. We could have avoided it, but to do so
would have made a simple development project very complex. I don't remember
what the issue was, but it was an unusual situation. Well, another
developer insisted that we should not even consider smart coding.
I pointed out that the resulting system would be very difficult to
understand and maintain, but he had always believed in "no smart
coding" and could not be convinced.
That's usually the right answer. But remember that smart coding is
all around us and is sometimes a good thing. Here are a few examples.
Many fields hold "yes" or "no" values, coded as "Y" and "N." Is
that an example of smart coding? Absolutely. We could use "1" and
"0" (although even that could be considered "smart"), or "5" for
yes and "X" for no. But it's far more convenient to remember Y/N
than 5/X. Even the database structure is full of smart coding and
it's staring us in the face all of the time. For example, take
a table name such as PS_PERSONAL_DATA. To avoid all smart coding
we could have named it OBJECT_00001 instead, but that would certainly be
a step backwards. The same goes for field/column names, page names,
and every other object in the entire system. Before starting with
PeopleSoft, I worked for a firm whose software product
contained very little smart coding. Everything was identified by
a random 4-digit number. To make setup easier, the company created
a separate "setup system," but it was so large and slow that
everybody tried to remember the 4-digit numbers to save time.
While avoiding smart coding whenever possible, let's be glad
that PeopleSoft didn't use that type of design!