Share via


Workflow-Enabled Database Tables

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.

When you add workflow to a database, the Workflow Designer for SQL Server adds the modStateID column to your main table and adds a number of new tables that control workflow to the database. For applications created using a template, the template defines the application's structure and its associated components.

The tables in a workflow-enabled database fall into two categories: internal workflow system tables and user-defined tables.

Table type Description
Internal workflow system tables Store information about the schema or structure of the application. These tables are used internally to implement workflow rules, lookups, and so on.
User-defined tables Hold the data for the application.

Table Naming Conventions

Internal workflow system tables   Each application has system tables with predefined names: modObjects, modColumns, modPermissions, modProperties, and modObjectTypes. Those names are reserved and cannot be used by user tables. In addition, there are a number of reserved names associated with stored procedures, triggers, and so on.

For each lookup table, the Workflow Designer for SQL Server provides a set of stored procedures. These procedures take as a parameter the user table names or column names and return the name of the associated lookup table. Lookup table names are prefixed with the name of the associated user table. For example, the lookup table storing workflow information for the Issues user table is called IssuesWorkflow.

User-defined tables   Table names are user-defined and should conform to the syntax for SQL identifiers. Workflow Designer for SQL Server supports both quoted and unquoted identifiers. The length of an SQL identifier is limited to 128 characters.

Note   When naming your workflow application and your database tables, do not use reserved keywords or the names of system tables. For best results, always use unique names. For example, naming your database "Replication," which is a reserved keyword, causes internal stored procedures to fail.

The following is an example of the types of tables in a typical workflow application, using the Issue Tracking sample application as a model.

Table Table type Description
modColumns system table Stores the application table columns and their properties.
modObjects system table Stores the application tables and defines hierarchies.
modObjectTypes system table Defines the lookup table for object types.
modPermissions system table Defines the sets of columns with related value domains for the entire application.
modProperties system table Defines name/value pairs for the workflow-enabled database.
modUserRoles user table Associates user names with roles.
Issues user table (main) Holds the root rows of each issue. The schema is defined by the application developer and can be customized.
Comments user table (detail) Holds the comment rows for each issue. The schema is defined by the application developer and can be customized.
IssuesWorkflow lookup table Stores all the states of the workflow process for the Issues table. This table is created when you add workflow to your database.
IssuesStateLookup lookup table Stores keywords for the State column in the Issues and Workflow tables. This table is created when you add workflow to your database.
IssuesAuthorLookup lookup table Stores keywords for the Author column in the Issues table. This table is created when you add workflow to your database.
IssuesProductRelation lookup table Defines an individual relationship between the values of the Product, Component, and Sub-component columns. This table is created when you add workflow to your database.

See Also

ModSystem 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 | The Lookup Table | Creating a Table Hierarchy