[Net 2000 Ltd. Home][DataBee Home][DataBee Manual][DataBee FAQ]

DataBee Table-To-Table Rules

Table-To-Table rules are used by the Set Extractor application to extract the dependent rows from a target table for each row already extracted from a source table. For example, consider a situation in which a table called DTB_INVOICE_LINE has had some rows extracted for it - possibly by a Where Clause rule. If the schema logic requires that it is not possible to have rows in present the DTB_INVOICE_LINE table without also having supporting rows in the DTB_CUSTOMER table, then a Table-To-Table rule must exist to enforce that relationship. In such a rule, the DTB_INVOICE_LINE table will be the source in the relationship and the DTB_CUSTOMER table will be the target. The rule would be shown on the Set Designer display as:

Assuming that the data rows in the two tables are related by a common field called CUSTOMER_ID, then in spoken words, the above rule is stating:

For every row previously extracted for the DTB_INVOICE_LINE table, extract the rows from the DTB_CUSTOMER table where DTB_INVOICE_LINE.CUSTOMER_ID=DTB_CUSTOMER.CUSTOMER_ID

In other words, this rule enforces database logic which states that it is not possible to have a row in the DTB_INVOICE_LINE table which does not have an equivalent supporting row in the DTB_CUSTOMER table. If rows are extracted for the DTB_INVOICE_LINE table, then the appropriate rows must also be extracted for the DTB_CUSTOMER table. Duplicates are always automatically removed, so if a DTB_CUSTOMER record was previously extracted then it will not be extracted again.

Tables in the schema can be involved in multiple Table-To-Table rules as both source and targets. When the extraction set is executed, the rules will activate automatically to ensure that referential correctness is maintained. For example, assume we have a set of rules as follows:

Rule ID       Rule
0001          Where Clause Rule extracts 25% of TABLE_A
0002          TABLE_A -> TABLE_B
0003          TABLE_A -> TABLE_C
0004          TABLE_C -> TABLE_D
0005          TABLE_D -> TABLE_A

In the simple set of rules above, rows will initially be extracted for TABLE_A by the Where Clause rule 0001. Rule 0001 will execute only once and will never run again. The following Table-To-Table rules will, however, run repeatedly as is necessary. After rows have been extracted for TABLE_A by rule 0001 then rules 0002 and 0003 must activate as their source table (TABLE_A) now has extracted rows which have not been processed. After rules 0002 and 0003 activate, rows will be extracted for TABLE_B and TABLE_C. Since TABLE_B is not involved in any rules as a source it requires no further processing. However, rule 0004 now needs to execute in order to ensure TABLE_C has the supporting rows it requires. After rule 0004 executes, rule 0005 will need to activate in order to ensure that all of the rows extracted for TABLE_D have supporting rows.

Note that the target of rule 0005 is TABLE_A - in this case the rules form a loop. This sort of looping effect is extremely common in extraction sets - in fact, it is rare not to see it. The DataBee Set Extractor is quite sophisticated, any duplicate rows extracted for TABLE_A by rule 0005 will be discarded. If any new rows are extracted for TABLE_A, then the sequence of rules 0001, 0002, 0003, 0004, 0005 will fire again to process those new rows. After repeated activations of the rules, all of the data rows which are referentially required to be present will have been extracted. At this point the extraction set has finished and all rows which need to be present in the subset schema will have been identified. In some cases, poorly designed rules will continue cycling until all rows are extracted from the tables. This is called an extraction set explosion and usually means that one of the rules in the set is inappropriate and will need to be dropped or modified.

Types of Table-To-Table Rule

The Appearance of Table-To-Table Rules on the Set Rules Tab

The above screen shot shows how Table-To-Table Rules are displayed in the Set Designer Set Rules tab. Notice the two leading characters in front of each rule number. In stage one and stage three rules, these characters determine the rule block of the rule. Table-To-Table rules always execute in stage two and the rule block digits are used to provide a visual indication of the type of relationship the rule enforces. The values can be either fk or tt. If the rule block is fk this means the rule enforces a foreign key relationship. Similarly, a rule block of tt means the rule was manually added by the person building the extraction set and hence it enforces a logical relationship. Note that if the foreign keys change in the source schema, the collection of fk based rules can be refreshed using the tools on the Options tab of the Edit Rule Controller form.

Also of note is the icon next to the Rule. The icon provides a quick visual indicator that the rule is a Table-To-Table rule. However, notice that the icons for rules tt-0032, tt-0033 and tt-0034 are slightly different. The small lowercase b in the icon indicates that the rule was created in bulk by one of the Set Designer tools. For Table-To-Table rules, the tool usually used for this purpose is the Bulk Rule Generator Tool.

Self Referential Relationships

Sometimes rows in a table are related to each other by a self-referential relationship. This means, as an example, that if rows are extracted for TABLE_A then supporting rows must also be extracted from TABLE_A. The DataBee software will handle this case internally and optimize the execution as is appropriate. A Table-To-Table rule enforcing a self-referential relationship is created normally and no special consideration needs to be given to it.

Cross Schema Table-To-Table Rules

By default, Table-To-Table rules are created between tables within the same schema. In some cases it is necessary to create a relationship between tables in different schemas. Effectively this means that rows extracted for a table in one schema require supporting rows to be extracted in a table in a different schema. It is possible to create a Cross Schema Table-To-Table rule in which the source and target tables are located in different schemas. Since the schema information is held in the Rule Controller, and two schemas are required, multiple rule controllers must be implemented prior to creating a cross schema rule. If multiple Rule Controllers are present, the All Schemas button below the right hand table name panel on the New Table-To-Table Rule Form can be pressed to show a list of the tables in the other schemas. Once those tables are visible, a Cross Schema Table-To-Table rule can then be created using the normal method. Note that the Cross Schema Table-To-Table rule is always created in, and owned by, the Rule Controller and schema which owns the source table in the relationship.

It is possible to create Cross Schema Table-To-Table rules between schemas which are not in the same database. This is an advanced topic and the DataBee Support Team should be contacted for advice prior to implementation.

Notes on Table-To-Table Rules

Table-To-Table rules are created by launching the New Table-To-Table Rule Form using the New Extraction Rule button located on the bottom right of the main Set Designer form.

How to Create a New Table-To-Table Rule

[Net 2000 Ltd. Home][DataBee Home][DataBee Manual][DataBee FAQ]