There is something that has been annoying me for quite a while. It is one of those niggling little things that we have been doing for so long that we take it for granted. We do it for historical reasons and take it for granted that that is the way we have to do it. I am talking about the naming conventions we use on our databases.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Figure 1 shows a snippet of what I am talking about. We have a field reference file (FRF) and a physical file (PF) that refers to it. The field names on the FRF are a maximum of four characters long because we use a unique two character prefix on the PF to identify the file to which the field belongs. Does this look familiar? OK, maybe your database uses a suffix instead of a prefix.
[TABLE]The length of the field names had to do with good old RPG III where the maximum length of a field name could not exceed six characters. We all know that RPG IV can handle the full 10-character field name and, of course, we are starting to use these in our databases.
But why the need for a unique identifier? The reason being that all fields in an RPG program are global, so we need to know which file a field came from and we need to ensure that input from one file does not accidentally overwrite input from another file -- which is what would happen if we had two files with fields of the same name.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Figure 2 shows the DDS for two files (a Customer File and a Supplier File), both of which contain a field called NAME.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Figure 3 shows an RPG program that opens these two files and reads a record from each. When the program reads a record from th
To continue reading for free, register below or login
To read more you must become a member of Search400.com
e Customer file it will place a value in the NAME field, then when it reads a record from the Supplier file it will overwrite the value in the NAME field -- hence the requirement for a unique identifier on each file.
This seems like a very reasonable premise, but it is using a very large hammer to crack a very small nut and leads us into some coding practices that may cause larger problems then the one they aim to cure.
Let us start with the database. It is a relational database. It is a normalized relational database. This means that a column (field) should be defined in one table (physical file) and one table only -- the only exception being "key," fields which are used to identify relationships, e.g. an invoice number on both the invoice header and invoice detail files.
The instance shown in Figures 2 and 3 would be the result of a badly designed database. The Customer name and Supplier name are not the same entities -- although they are both names. Think of NAME as being a data type as opposed to a field and it should have been taken care of in the field reference file, as shown in Figure 4. The NAME field is defined as a "generic" name, and the actual fields (CUSNAME and SUPPNAME) refer back to it for their definitions.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
So, in an RPG program the only fields that should be "double defined" are key reference fields, and more then likely we will have the same value in them, since we use them to link between files (e.g. use a CHAIN operation to get the record from the invoice header and use a READE loop to read all corresponding records from the invoice details file). No big benefit here, from unique identifiers.
On the other hand, think of the problems that unique identifiers cause. We have all had that old chestnut during testing where we can't understand why we are getting that duplicate record error until we notice that we are writing records with a blank key field -- because we forgot to move the "parent" key field to the "child" key field. It would not have been a problem if we were using the same field name on both files.
Worse still, what about using unique field names on display files and print files -- now we have to move all the fields, not just key reference fields. You just have to remember that when you add a new field to a file and any corresponding display files, you must put in the appropriate MOVE operations as well.
There seems to be much less code when we are not using unique identifiers.
Now, there are some cases where we might have a problem with the same field name on multiple files. After all, what we do in our programs can often be weird and wonderful. The example in Figures 2 and 3 could well arise if the two files came from applications provided by different software vendors.
If you need unique field names, why not try one of the following:
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
I was always taught that my design should handle what normally happens and that I should code for the exception. In RPG a clash of field names should be the exception and not the norm. By having unique identifiers on our database field names, we are treating these exceptions as if they are the norm, and that in turn forces us to have extra code in nearly all our programs to handle the unique naming.
---------------------------
About the author: Paul Tuohy is CEO of ComCon, an iSeries consulting company. He is the author of Re-Engineering RPG Legacy Applications and is one of the quoted industry experts in the IBM Redbook "Who Knew You Could Do That With RPG IV?"
==================================
MORE INFORMATION
==================================
