Skip to content

Alias Tables

Application requirements can arise where the same physical table needs to be used more than once within a single view’s table 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 rows are maintained in the same table. In this case, a child row relates to the same physical row 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 rows are stored in a single table. In this case, a physical row must relate to itself.

  • A single order requires two addresses – a bill-to address and a ship-to address. Both of these address rows have a parent row of their own (region) which will be different for each address row (bill-to region and ship-to region). In this case, a child row relates to the same physical parent row two times which, in turn, will relate to the same physical grandparent row 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 tables, while logically distinct, need to be mapped to the same physical table.

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 as alias table(s). This identification technique (Set_Attribute, DF_FILE_ALIAS) is described below.

Creating an Alias Table

An alias table is created via these steps:

  1. Add a new entry to the filelist and add a .FD file for it.
  2. Create one or more Data Dictionaries for the new alias table.
  3. Set alias table modes.
  4. Set alias table relationships.

Add an Entry to the Filelist and Create a .FD File for It

You can add an alias entry for a table in the filelist via the Add An Existing Table option on the Table Explorer's context menu.

Type or select the physical table in the File Name form, leave the Root Name as the default, and enter the alias you wish to use to refer to this table in your code as the Table Name. In short, the File Name and Root Name should be the same as for the "Master" table, and the Table Name is what uniquely identifies the alias table.

At this point, you have two entries in your filelist pointing to the same physical table, but with different Table Names.

Create One or More Data Dictionaries for the New Alias Table

Add one or more Data Dictionaries for the alias table via the Create New Data Dictionary option on the Table Explorer's context menu.

You may now view and edit any Data Dictionary settings using the Studio's Data Dictionary Modeler. If your purposes allow it, attempt to make the alias table a "read-only" table.

Set Alias Table Modes

You will need to identify this table as an alias table and identify the original table as a master table. You can do this in the source code of the Data Dictionary in the Studio's code editor as follows:

Add the following lines of code just above the DataDictionary class declaration line:

Open {MainTableName}
Set_Attribute DF_FILE_ALIAS of MainTableName.File_Number to DF_FILE_IS_MASTER
Set_Attribute DF_FILE_ALIAS of AliasTableName.File_Number to DF_FILE_IS_ALIAS

A typical code section might look like the following:

Open Customer  // make sure that the main table is open
Set_Attribute DF_FILE_ALIAS of Customer.File_Number to DF_FILE_IS_MASTER
Set_Attribute DF_FILE_ALIAS of Custome2.File_Number to DF_FILE_IS_ALIAS
//
Class Custome2_DataDictionary is a DataDictionary
Procedure Construct_Object
:

You must make sure you mark your tables as master and alias. Normally, both of these settings can be made within the alias table’s Data Dictionary. This makes good sense because the main, non-alias, table does not need to be recognized as a master until an alias table is used. We add the open command to make sure that the master table is already opened (the table cannot be designated a master table if it is not open). This way, you do not have to worry about the ordering of tables in your program.

Closing and (Re)Opening Master and Alias Tables

If you are using techniques in your application that close and open tables during program execution, you must ensure that the Master and Alias attributes are reapplied after each time you open any of the affected tables, as the previous attributes are discarded when a table is closed.

Set Alias Table Relationships

Creating relationships to an alias table (where the alias is defined to be a related-to parent) is easy. After an alias table has been properly created, child tables may relate to the alias as they would to any other table. This is how alias tables are normally used.

If your master table relates to other tables (i.e., it is the child of a relates-to relationship) or your alias tables need to relate to a parent table, special care must be taken. Relationship information is stored in the physical table’s header. If a relationship is created in the master table, that relationship will also exist in the alias table. Similarly, if you change a relationship in the alias table, that change will also be made for the master table. You are changing the same physical table structure. Any change in one table affects the other.

Normally, you will not want a master and alias row to always point to the same physical parent. If relationships exist in the master table, you will want to clear them or change them in the alias. While this cannot be done by changing the relationship in the table, it can be done at runtime using the Set_Relate command. The Set_Relate command is "soft"; it only changes relationships at runtime—which is exactly what is needed with alias tables.

The Set_Relate command(s) should be added to your Data Dictionary just above the DataDictionary class declaration line and below the place where the Master and Alias Attributes are set.

Clearing a Relationship in an Alias Table

An alias table will usually not require all of the same parent tables that its master-table counterpart does. Alias tables are often read-only and will have no required parents. When the alias table is opened, it inherits all of the table relationships of its master. These can be cleared by using the Set_Relate command as follows:

Set_Relate AliasTableName.ColumnName to |FN0,0

Where:

  • AliasTableName.ColumnName is the name of the relating-from column to set.
  • |FN0,0 is a special symbol that indicates that the relationship should be cleared.

A typical code section might look like the following:

Set_Relate Custome2.VendorId to |FN0,0

Establishing a New Relationship in an Alias Table

Sometimes an alias table will need to relate to a different parent table than its master-table counterpart does. Most likely, the different parent table will be an alias table itself. The new relationship would be set using the Set_Relate command as follows:

Set_Relate AliasTableName.ColumnName to ParentTable.Field_Name

Where:

  • AliasTableName.ColumnName is the name of the relating-from column to set.
  • ParentName.ColumnName is the name of the parent table and column to relate to.

A typical code section might look like the following:

Set_Relate Custome2.VendorId to Vendor2.Id

Setting Data Dictionary Required Parent Tables Lists

If any new relationships are established, you must remember to adjust your Data Dictionary required parent tables structure list to reflect these changes. An alias table will almost always have a relating-to child table (this is why you created the alias table). If a hard or soft relationship is created between the child and the alias parent, this dependency must be added to the child’s required parent tables list. If the alias table relates to a parent table, this dependency should be added to the alias table’s required parent tables list. The required structures are set in a DD using the Studio Data Dictionary Modeler's Structures tab page.

Sample Alias Structures

Simple Alias Structure

A single order requires two addresses—a bill-to address and a ship-to address. Both of these address rows are maintained in the same physical table. The main address table is named Address. We will create an alias table named Address2. Assume that Address relates to a table named Region and that we will wish to remove this relationship in the alias. The steps for creating this alias table are:

  1. Add the alias, Address2, to your filelist (which also creates the .FD file) and create the alias DD file.
  2. In the Address2 Data Dictionary source code, define the master and alias tables and clear the existing table relationship. The code will look like this:
Open Address // make sure that the main table is open
Set_Attribute DF_FILE_ALIAS of Address.File_Number to DF_FILE_IS_MASTER
Set_Attribute DF_FILE_ALIAS of Address2.File_Number to DF_FILE_IS_ALIAS
// clear the relationship in the alias table
Set_Relate Address2.Region_Key to |FN0,0
Class Address2_DataDictionary is a DataDictionary
:
  1. In the child table (Order) that relates to the Address2 alias table, create a "hard" (using the Studio's Table Editor) or soft (Set_Relate) relationship to this table. On the Order Data Dictionary Structures tab page, make sure that both tables, Address and Address2, are added to the required parent tables list.

Recursive Alias Structure

You maintain a list of service technicians. Each technician is assigned a backup technician (who is called when the primary technician is unavailable). The main table is Tech, and the alias table will be named TechBkup. The steps for creating this alias table are:

  1. Add the alias, TechBkup, to your filelist (which also creates the .FD file) and create the alias DD file.
  2. In the TechBkup Data Dictionary source code, define the master and alias tables. The code will look like this:
// TechBkup
Open Tech  // make sure that the main table is open
Set_Attribute DF_FILE_ALIAS of Tech.File_Number to DF_FILE_IS_MASTER
Set_Attribute DF_FILE_ALIAS of TechBkup.File_Number to DF_FILE_IS_ALIAS
Class TechBkup_DataDictionary is a DataDictionary
:
  1. In Tech, create a soft (Set_Relate) relationship to the alias table. This must be a soft relationship because a hard relationship would appear in both the main and alias table. This would be done as follows:
// Tech
// define soft relationship to alias table
Set_Relate Tech.Bkup_Tech to TechBkup.Tech_Key
Class Tech_DataDictionary is a DataDictionary
:
  1. On the Tech Data Dictionary Structures tab page, make sure that TechBkup is added to the required parent tables list.

Multi-level Alias Structure

A single order requires two addresses—a bill-to address and a ship-to address. Both of these address rows have a parent row of their own (Region), which will be different for each address row. The main tables Address and Region already exist. We need to create two alias entries, Address1 and Region1, as follows:

  1. Add the alias, Address2, to your filelist (which also creates the .FD file) and create the alias DD file.
  2. Add the alias, Region2, to your filelist (which also creates the .FD file) and create the alias DD file.
  3. In the Region2 Data Dictionary source code, define the master and alias tables for region. The code will look like this:
// Region2
Open Region  // make sure that the main table is open
Set_Attribute DF_FILE_ALIAS of Region.File_Number to DF_FILE_IS_MASTER
Set_Attribute DF_FILE_ALIAS of Region2.File_Number to DF_FILE_IS_ALIAS
Class Region2_DataDictionary is a DataDictionary
:
  1. In the Address2 Data Dictionary source code, define the master and alias tables and set a relationship between Address2 and Region2. The code will look like this:
// Address2
Open Address // make sure that the main table is open
Set_Attribute DF_FILE_ALIAS of Address.File_Number to DF_FILE_IS_MASTER
Set_Attribute DF_FILE_ALIAS of Address2.File_Number to DF_FILE_IS_ALIAS
// set the relationship between address2 and region2
Set_Relate Address2.Region_Key to Region2.Region_Key
Class Address2_DataDictionary is a DataDictionary
:
  1. On the Address2 Data Dictionary Structures tab page, add Region2 to the required parent tables list.
  2. In the child table (Order) that relates to the Address2 alias table, create a "hard" (using the Studio's Table Editor) or "soft" (Set_Relate) relationship to Address2.
  3. On the Order Data Dictionary Structures tab page, make sure that both tables, Address and Address2, are added to the required parent tables list.

Deleting an Alias Table

Remember that an alias and a master table share the same physical table. If you delete or erase data from an alias table, you will also delete or erase data from your main table. Normally, you do not delete an alias table. You may remove an alias table as follows:

  1. Remove the alias table from your filelist using the Remove Table option on the Table Explorer's context menu. Select the Remove Table radio in the Remove Table dialog.
  2. Manually delete the alias .FD and .DD tables from your disk. These should be found in your DDSrc directory or the current workspace.
  3. Change any child tables that related to this table. Remove any relationships from the table and remove any parent relationship requirements from their Data Dictionaries.

Tips

  • Keep in mind that any table may have several Data Dictionaries. You must ensure that the same Master-Alias code is applied to all Data Dictionaries that need it. Consider subclassing your Data Dictionaries or using a #Include file with the duplicated code to allow yourself to maintain the code in a single place.
  • If you are using techniques in your application that close and open tables during program execution, you must ensure that the Master and Alias attributes are reapplied after each time you open any of the affected tables, as the previous attributes are discarded when a table is closed.
  • If you are using techniques in your application that close and open tables during program execution, you must ensure that any soft relationships are reapplied after each time you open any of the affected tables, as the previous attributes are discarded when a table is closed.

See Also