The Lookup Table
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
For columns of type enumeration, the enum keywords are stored in a lookup table, and the corresponding keyword lookup IDs are stored in the user table columns. This lookup schema has two advantages. First, it reduces the storage space for enum columns. Second, it makes it easier to change the keyword, because the keyword name is stored in one place. This is an important feature for application customization. However, the tradeoff for this schema is that when you work with your workflow application, you must provide views that resolve lookups rather than work with the table directly.
Each lookup column has an associated lookup table. This makes it easier for the Workflow Designer for SQL Server to connect directly to the database and manage keywords for enum columns without having to know the column ID.
The link between the lookup columns in the user table and the lookup table is stored in the modColumns table.
Lookup tables have the ID column set up as primary key. All the tables using lookups, such as user tables and column relation tables, should store the ID and define it as a foreign key referring back to the lookup. The primary key-foreign key constraint makes sure invalid IDs are not inserted into the application.
The following table lists the lookup table columns and their data types and provides a brief description of each.
| Column | Data type | Description |
|---|---|---|
| Id | identity (int) | Unique identifier for each keyword used in the application. |
| Name | nvarchar(128) | Lookup value name. |
Users can add new keywords to a column. This feature is controlled by the permissions set on each lookup table.
See Also
Workflow-Enabled Database Tables | The modColumns Table | The modObjects Table | The modObjectTypes Table | The modPermissions Table | The modProperties Table (Workflow-Enabled Database) | The modUserRoles Table | The Workflow Tables | The WorkflowActions Tables | Database Tools and Technologies