Detail Data Tables
Concept
Ordinarily, when a Template (Global or Subledger) is saved, STEPforward creates one, and only one, database table with the structure based on the composition of the template. However, if the Template includes Detail field, STEPforward creates not one, but two tables:
Master table based on all fields in the template except for the Detail field, and
Detail table based on the composition of the Detail field alone
It thus creates a pair of tables that implement the often-required Master-Detail (a.k.a. one-to-many) relationship, where one record in the Master table is related to a group of records in the Detail table. Such arrangement of tables can be used in Purchase Ordering, Invoicing, Patient Billing etc. systems.
General structure
When a new Template with the Detail data field is saved, the Master table that is created along with it is based on that part of the template that includes only regular Data fields. The Master table's makeup is covered in Global Tables section; however, one more system-defined column is added to the table:
Column | Generalized data type | Use |
SF_Status | CHAR(1) | Completion status of the related Detail data |
The contents of this field is set to 'C' ("Complete") if every related row in the Detail data table is marked as complete (see below); otherwise it is set to 'S' ("Suspended"). If no Detail data exist for the given row in the Master table, the status is set to 'C'.
Detail Table structure
When the Template with Detail data field is saved by STEPforward, one additional table (Detail table) and two more views are created in the database. The table name is based on the name of the template, with SF_DT prefix attached to it; the views use prefixes SF_DV and SF_VV. For example, a template named "Invoice" with a Detail field will have the table SF_DTInvoice and the views SF_DVInvoice and SF_VVInvoice created when it is saved the first time, in addition to the regular SF_MTInvoice table (called "Master table" in this context) and SF_MVInvoice view.
The Detail table contains one column for every column of the Detail field in the Template. Data types of the columns are set in the same manner as the data types of regular columns in the Master table, as described in the Global Templates section.
In addition to the user-defined data fields, STEPforward adds the following system-defined columns to the Detail table:
Column | Generalized data type | Use |
SF_ID | INT | Unique number assigned to each row in the table |
SF_TX | INT | ID of the related row in Master table (SF_ID) |
SF_Line | SMALLINT | Number of the row in the same SF_TX group |
Here SF_TX is a number of the row in the Master table to which the row in the Detail table is related. In the Master table, it is recorded as SF_ID. More than one row in the Detail table can have the same value in SF_TX field; all such rows belong to the same group and are numbered from 0 up in their SF_Line field.
Views
Every time STEPforward creates a Detail table for the Template, it also creates two views on all of its columns, as illustrated in the following example that is based on a rather simplistic Invoice template:
Master table (SF_MTInvoice):
create table SF_MTInvoice (SF_ID int not NULL primary key,SF_Status char(1) NULL,Sale_date char(8) NULL,SF_ID11 int NULL,Address varchar(40) NULL,Customer_number varchar(10) NULL,SF_UserId varchar(255) NULL,SF_DateChanged char(8) NULL,SF_TimeChanged char(6) NULL,SF_TimeStamp smallint NULL)
Master table view (SF_MVInvoice):
create view SF_MVInvoice (SF_ID,SF_Status,Sale_date,State_Prov,Country,City,Address,Customer_number,SF_UserId,SF_DateChanged,SF_TimeChanged,SF_TimeStamp) as select SF_MTInvoice.SF_ID,SF_MTInvoice.SF_Status,SF_MTInvoice.Sale_date,SF_MTCityStat.State_Prov,SF_MTCityStat.Country,SF_MTCityStat.City,SF_MTInvoice.Address,SF_MTInvoice.Customer_number,SF_MTInvoice.SF_UserId,SF_MTInvoice.SF_DateChanged,SF_MTInvoice.SF_TimeChanged,SF_MTInvoice.SF_TimeStamp from SF_MTInvoice,SF_MTCityStat where SF_MTCityStat.SF_ID = SF_MTInvoice.SF_ID11
Detail table (SF_DTInvoice):
create table SF_DTInvoice (SF_ID int NULL, SF_TX int NULL, SF_Line smallint NULL,Part_number int NULL,Description varchar(100) NULL, Price money NULL,Quantity smallint NULL)
The first view created for the Detail table is conceptually similar to the view created on Master table; it includes all columns natively located in the Detail table plus link columns to the fields in other (linked) tables. Our example does not make use of the linked fields in Detail field:
create view SF_DVInvoice (SF_ID,SF_TX,SF_Line,Part_number,Description,Price,Quantity) as select SF_DTInvoice.SF_ID,SF_DTInvoice.SF_TX,SF_DTInvoice.SF_Line,SF_DTInvoice.Part_number,SF_DTInvoice.Description,SF_DTInvoice.Price,SF_DTInvoice.Quantity from SF_DTInvoice
The second view joins together the Master and Detail table, resolving the links in SF_MTInvoice:
create view SF_VVInvoice (SF_ID,SF_Status,Sale_date,State_Prov,Country,City,Address,Customer_number,SF_UserId,SF_DateChanged,SF_TimeChanged,SF_TimeStamp,SF_TX,SF_Line,Part_number,Description,Price,Quantity) as select SF_MTInvoice.SF_ID,SF_MTInvoice.SF_Status,SF_MTInvoice.Sale_date,SF_MTCityStat.State_Prov,SF_MTCityStat.Country,SF_MTCityStat.City,SF_MTInvoice.Address,SF_MTInvoice.Customer_number,SF_MTInvoice.SF_UserId,SF_MTInvoice.SF_DateChanged,SF_MTInvoice.SF_TimeChanged,SF_MTInvoice.SF_TimeStamp,SF_DTInvoice.SF_TX,SF_DTInvoice.SF_Line,SF_DTInvoice.Part_number,SF_DTInvoice.Description,SF_DTInvoice.Price,SF_DTInvoice.Quantity from SF_MTInvoice,SF_MTCityStat,SF_DTInvoice where SF_MTCityStat.SF_ID = SF_MTInvoice.SF_ID11 and SF_MTInvoice.SF_ID = SF_DTInvoice.SF_TX
Thus, in the case of Templates with Detail field, STEPforward creates not one, but three views. Why so many?
The first view (SF_MVInvoice) is used by STEPforward whenever the retrieved fields are limited to the Master fields of the template. In other words, when the data searched/loaded from the table involve Master fields only, SF_MVInvoice view offers a better performance, sparing the database server from doing needless joins.
The second view (SF_DVInvoice) is used by STEPforward whenever the retrieved fields are limited to the Detail fields of the template. I.e., when the data searched/loaded from the table involve Detail fields only, SF_DVInvoice view offers a better performance, just like SF_MVInvoice in the previous case.
Finally, the third view (SF_VVInvoice) provides a composite image of the Master-Detail pair, forming a cartesian product of the two tables. It complements the Detail data with the full set of the corresponding Master data for each Detail row. While being more resource-hungry than the previous two views, SF_VVInvoice simplifies searches for the Detail data that involve fields from the Master table (i.g. find all items sold to the given customer over certain period of time).
STEPforward decides which one of these views to use, depending on the composition of the query to be performed.