Virtual and Direct Columns Definition
Virtual and Direct Columns DefinitionA Pick Data Provider table consists of two types of columns:
Direct
Virtual
Direct columns are columns that physically exist in the table. Virtual columns are columns calculated from direct or other virtual columns in the table, or use direct or virtual columns that exist in a secondary table. The use of columns which exist in a secondary table requires that the primary table contain a primary key (foreign key) to the table. Direct and virtual columns are managed by using the Administration console.
See Column Class for more information.
In the example below, there are two tables:
The Parts table has direct columns containing a part description, quantity on hand, and a vendor ID, which is the foreign key to the Vendor table.
The Vendor table contains direct columns with the vendor’s name, phone number, and phone number type, such as fax, mobile, work direct.
|
Parts Table |
||
|
Description |
Quantity |
Vendor ID |
|
Vendor Table |
||
|
Vendor Name |
Vendor Phone |
Vendor Phone Type |
By creating virtual columns from the Parts table to the Vendor table using the Vendor ID as the primary key to the Vendor table, any of the columns in the Vendor table can be made to appear as if it is in the Parts table.
There are two advantage of using virtual columns:
To obtain all of the information only one table needs to read.
Information in the second table can be shared between many primary tables, thus reducing the amount of redundant data. Virtual columns must be updated separately from the primary table. To update all direct and virtual columns, two updates are necessary.
The Part table update consists of the Description, Quantity, and Vendor ID and the Vendor table update consists of the Vendor Name, Vendor Phone, and Vendor Phone Type.
|
Parts Table |
|||||
|
Description |
Quantity |
Vendor ID |
Vendor Name |
Vendor Phone |
Vendor Phone Type |
Using the Vendor table in this example, our analysis of our search patterns found that we often look for fax phone numbers by area code. Therefore, we have created a virtual column based on the phone number type and the area code portion of the phone number.
|
Vendor Table |
|||
|
Vendor Name |
Vendor Phone |
Vendor Phone Type |
Phone Type + Area code |
See Column Class for more information.