Application requirements arise where the same physical table needs to be used more than once within a single Data Dictionary object structure. This occurs when the same physical table serves more than one logical purpose. Some examples of this are:
A single order requires two addresses – a bill-to address and a ship-to address. Both of these address records are maintained in the same table. In this case, a child record relates to the same physical table two times.
You maintain a list of service technicians. Each technician is assigned a backup technician (who is called when the primary technician is unavailable). Both main and backup technician records are stored in a single table. In this case, a table must relate to itself.
A single order requires two addresses – a bill-to address and a ship-to address. Both of these address records have a parent record of their own (region) which will be different for each address record (bill-to region and ship-to region). In this case, a child record relates to the same physical parent table two times which, in turn, will relate to the same physical grandparent table two times.
In all of the above cases, each of the logical tables is unique and serves a distinct purpose in your table hierarchy. The last example would be structured as follows:
The tables, while logically distinct, need to be mapped to the same physical file.
A technique called alias tables supports this ability. An alias table is created by placing the same physical table in multiple locations in your filelist. An alias filelist entry is created using the same physical name as the main table (file name) while creating unique logical names (root name and table name). When the tables are opened within the same program, a separate table buffer is created for each logical table. Data from each table is read and written to the same physical table.
Using alias tables creates certain multi-user challenges. The same physical table cannot be locked twice. To avoid this condition, you must identify logical tables sharing the same physical table as one master table and the remainder, an alias table (or tables). This identification technique is described below.
Working with Alias Tables and DDs