Global Tables
General structure
When a new Global Template is saved by STEPforward, a table and a view are created in the database. The table name is based on the name of the template, with SF_MT prefix attached to it; the view uses prefix SF_MV. For example, a template named "Employee" will have the table SF_MTEmployee and the view SF_MVEmployee created when it is saved the first time.
The table contains one column for every field of the Template that was created by dragging-and-dropping the "New field" object from Palette to the Workspace. Data types of the columns are derived from the data types of the Template fields set in the Field Properties Inspector according to the following chart:
Template field data type | Generalized data type | Sybase field data type |
Text | CHAR(N) | char(N) for fixed size |
VARCHAR(N) | varchar(N) for variable (Max) size | |
Integer | INT | int for 4-byte sized fields |
SMALLINT | smallint for 2-byte sized fields | |
TINYINT | tinyint for 1-byte sized fields | |
Float | FLOAT | float |
Money | MONEY | money |
Image | BLOB | image |
Note | BLOB | image |
Date | CHAR(8) | char(8) |
Time | CHAR(6) | char(6) |
Switch | LOGICAL | bit |
Subledger No | VARCHAR(N) | varchar(N) |
Generalized data types are tokens used by SFDataBase object when formatting the CREATE TABLE SQL command; the tokens are further translated into the concrete data type keywords by the RDBMS-specific adaptor (a Sybase example of such converted data types is shown in the third column).
In addition to the user-defined data fields, STEPforward adds the following system-defined columns to each table created for the Global Template:
Column | Generalized data type | Use |
SF_ID | INT | Unique number assigned to each row in the table |
SF_UserId | VARCHAR(255) | User-id of the user that last updated the row |
SF_DateChanged | CHAR(8) | Date of the last update of the row (CCYYMMDD) |
SF_TimeChanged | CHAR(6) | Time of the last update of the row (HHMMSS) |
SF_TimeStamp | SMALLINT | Version of the row (incremented with each update) |
Views
Every time STEPforward creates a table for the Global Template, it also creates a view on all of its columns. For example, when the SF_MTExample table is created with the command (Sybase version):
create table SF_MTExample (SF_ID int not NULL primary key, SF_SubledgerNumber varchar(10) NULL, Switch bit, TimeField char(6) NULL, DateField char(8) NULL, NoteField image NULL, ImageField image NULL, MoneyField money NULL, Float8 float NULL, Integer1 tinyint NULL, Integer2 smallint NULL, Integer4 int NULL, TextFixed40 char(40) NULL, TextMax40 varchar(40) NULL, SF_UserId varchar(255) NULL, SF_DateChanged char(8) NULL, SF_TimeChanged char(6) NULL, SF_TimeStamp smallint NULL)
the following view is created alongside with it:
create view SF_MVExample (SF_ID, SF_SubledgerNumber, Switch,TimeField, DateField, NoteField, ImageField, MoneyField, Float8, Integer1, Integer2, Integer4, TextFixed40, TextMax40, SF_UserId, SF_DateChanged, SF_TimeChanged, SF_TimeStamp) as select SF_MTExample.SF_ID, SF_MTExample.SF_SubledgerNumber, SF_MTExample.Switch, SF_MTExample.TimeField, SF_MTExample.DateField, SF_MTExample.NoteField, SF_MTExample.ImageField, SF_MTExample.MoneyField, SF_MTExample.Float8, SF_MTExample.Integer1, SF_MTExample.Integer2, SF_MTExample.Integer4, SF_MTExample.TextFixed40, SF_MTExample.TextMax40, SF_MTExample.SF_UserId, SF_MTExample.SF_DateChanged, SF_MTExample.SF_TimeChanged, SF_MTExample.SF_TimeStamp from SF_MTExample
While the concept of a view that is identical with its underlying table may seem redundant, its significance will become clear in the following section.
Linked fields
When a "Grab" field object is brought from Palette into the Template, it does not create a new column in the Template's table, but rather it creates a link to the table from which the "Grab" field was dragged. This link is maintained as a special column in the table where the row identifier of the linked-to row ("source row") is stored.
In the example to follow, the "Name" Global Template is created by dragging in FirstName, LastName and Address fields as "New field" objects, but the City field is dragged in as a "Grab" field from the City template. STEPforward creates the following table:
create table SF_MTName (SF_ID int not NULL primary key, FirstName varchar(20) NULL, LastName varchar(20) NULL, Address varchar(100) NULL, SF_ID1 int NULL, SF_UserId varchar(255) NULL, SF_DateChanged char(8) NULL, SF_TimeChanged char(6) NULL, SF_TimeStamp smallint NULL)
Here, the "City" column of character data type is not included in the command; instead, there is the column SF_ID1 of int type. The "missing" column is reinstated by the view created with the following SQL command:
create view SF_MVName (SF_ID, FirstName, LastName, Address, City, SF_UserId, SF_DateChanged, SF_TimeChanged, SF_TimeStamp) as select SF_MTName.SF_ID, SF_MTName.FirstName, SF_MTName.LastName, SF_MTName.Address, SF_MTCity.City, SF_MTName.SF_UserId, SF_MTName.SF_DateChanged, SF_MTName.SF_TimeChanged, SF_MTName.SF_TimeStamp from SF_MTName, SF_MTCity where SF_MTCity.SF_ID = SF_MTName.SF_ID1
The above view joins the two tables (SF_MTName and SF_MTCity) together at their SF_ID1 and SF_ID columns respectively, creating an appearance of the integrated table that contains not only the fields natively allocated in the SF_MTName table (FirstName, LastName and Address), but also the City column even though it really exists in some other table (SF_MTCity).
The net effect of this view can be illustrated in the following example:
Suppose the SF_MTCity table contains the following data:
SF_ID | City | Province | Country |
1 | Calgary | Alberta | Canada |
2 | Toronto | Ontario | Canada |
3 | Denver | Colorado | USA |
4 | San Jose | California | USA |
and the SF_MTName table contains these rows (leaving out Address for simplicity):
SF_ID | FirstName | LastName | SF_ID1 |
1 | Wolfgang | Rochow | 1 |
2 | Heidi | Barg | 4 |
3 | Alex | Molochnikov | 2 |
then the SF_MVName view will resolve the links as follows (substituting City column in place of SF_ID1 as set in the CREATE VIEW command):
SF_ID | FirstName | LastName | City |
1 | Wolfgang | Rochow | Calgary |
2 | Heidi | Barg | San Jose |
3 | Alex | Molochnikov | Toronto |
When STEPforward refers to the Name template as a view (SF_MVName) rather than a table (SF_MTName), it will "see" the linked field "City" as if it were a natural part of the table itself. The only difference is that it really exists in SF_MTCity table and can be used as a source of links for other tables as well.
Trains of linked fields
More than one linked field can be brought into the Global Template from the same source. All linked fields that originate from the same table are "collapsed" in the receiving table into a single link (similar to the example in the previous section), and expanded into the original fields by the view, as shown in the following example.
This example builds upon the previous example, adding two more linked fields from the same SF_MTCity table: Province and Country. The CREATE TABLE command in this case is exactly the same as before, and is not shown here. However, the CREATE VIEW command is different:
create view SF_MVName (SF_ID, FirstName, LastName, Address, City, Province, Country, SF_UserId, SF_DateChanged, SF_TimeChanged, SF_TimeStamp) as select SF_MTName.SF_ID, SF_MTName.FirstName, SF_MTName.LastName, SF_MTName.Address, SF_MTCity.City, SF_MTCity.Province, SF_MTCity.Country, SF_MTName.SF_UserId, SF_MTName.SF_DateChanged, SF_MTName.SF_TimeChanged, SF_MTName.SF_TimeStamp from SF_MTName, SF_MTCity where SF_MTCity.SF_ID = SF_MTName.SF_ID1
This view now adds the two new columns to the image of our SF_MTName table, resolving the links as shown below:
SF_ID | FirstName | LastName | City | Province | Country |
1 | Wolfgang | Rochow | Calgary | Alberta | Canada |
2 | Heidi | Barg | San Jose | California | USA |
3 | Alex | Molochnikov | Toronto | Ontario | Canada |
Multiple links and multi-level hierarchy
Linked fields can be brought into the Global Template from more than one source. In this case, each train of linked fields originating from one source is represented in the table by one link field named SF_IDN, where N is the number assigned to the link. Thus, the table can have columns SF_ID1, SF_ID2 etc. pointing to different linked rows in different sources.
Links can also be inherited through the hierarchy of views. That is, the Name template can itself serve as a source for other templates that make use of its fields. In this case, the native fields of the Name template will become the links of the first order, while the linked fields (City, Province and Country) will be the links of the second order (if brought into those templates). The system of views will hide the real origin and nature of the linked fields from the recipient templates, posing them as native fields in the source, regardless of their true location.
Benefits and liabilities of links
Linked fields allow the end user to normalize the database design by eliminating data redundancy, while shielding the user from the intricacies of the design of the relational database. Frequently repeated items (such as city of residence in the customer table, where the same city can be recorded for most of the customers) can be moved into one table, and linked from that table into those tables that make use of them.
Linked fields also provide a convenient way of centralizing and instantly distributing changes in data throughout the database. For example, a change of description of a given inventory part can take effect in all tables that reference this part, if the description field is linked into those tables.
However, the user should realize that multi-level linking of fields from one template into another, while providing a smooth flow of data, can tax the database server to the point where searches on the views with links become increasingly slow. The exact threshold of the number of individual links within the template and the level of nesting of links, where the loss of performance becomes noticeable, depends on the design of the particular hierarchy of templates/links, and will vary from one RDBMS to another. As a rough rule, the flatter is the structure of the links (i.e. the fewer levels of reference) the better is the performance. Our tests show that Sybase 11 can handle level of nesting of linked fields up to 4 without significant performance penalty.
NULL row and links
When a new global table is created by STEPforward, a row is added to the table. All fields in this row are set to NULLs, except for SF_ID field which is set to 0 (also, all other fields whose data types do not allow the NULL value are set to their permissible default values. For example, LOGICAL data type in Sybase is mapped to the bit type and set to 0, because the bit data type cannot contain NULL).
This NULL row is used in resolving links in newly added rows that contain link fields (i.e. SF_IDN fields that carry pointers to other rows). Because the contents of global tables in STEPforward is accessed via the view that joins the linked tables together, an unresolved link will cause the entire row to become inaccessible. That is, not only the linked data will be missing, but also all data in the native fields in that row will be lost as well.
The NULL row helps remedy this problem. If the link is not defined in such a row, it's SF_IDN field is left with the default value of 0. Thus, by default the link field points to the NULL row in the source table, allowing the table join to be completed and bringing in the contents of the native fields in the table.
Indexing
When a new global table is created by STEPforward, an index is created on its SF_ID field; this accelerates queries and joins that make use of this field. The exact syntax of the CREATE INDEX command varies from one RDBMS to another; the following example shows the command formatted for Sybase:
create index SF_Index1 on SF_MTExample (SF_ID) on SF_IndexSegment
This index is created in addition to the user-defined indices set through the Inspector panel.