Defining Hierarchy Schema

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.

Your workflow project typically includes a combination of main tables, detail tables, and lookup tables. As you develop your project, you can specify additional main tables as needed to enhance your workflow.

There are three types of tables that are displayed in the hierarchy: main, detail, and lookup tables.

   Main tables   are the root of data hierarchies. Main tables can be enabled for row-level permissions and workflow in a workflow application. For example, the Issues table in the Issue Tracking sample is a main table. It has workflow and row-level permissions enabled, and it contains information about each issue entered in the database.

   Detail tables   are child tables of main tables. The parent column for detail tables holds its parent table ID in the modObjects table in the modSystem database. Detail tables can hold multiple entities for one row in the parent table. They can be enabled for workflow. For example, the Comments table in the Issue Tracking sample is a detail table that holds comments associated with each issue in the Issues table.

   Lookup tables   are child tables of main or detail tables. Lookup tables have a many-to-one relationship with other tables in the project. For any column in a main or detail table that contains a defined set of keywords, the keywords can be stored in a lookup table, and the corresponding keyword lookup IDs stored in the main or detail table columns. In addition, the Workflow Designer uses lookup tables to manage core features. For example, the IssuesStateLookup table in the Issue Tracking sample is a lookup table that stores all the states of the workflow process for the Issues table.

Table Hierarchy Requirements

There are guidelines that must be followed when defining a table hierarchy. Although you can have multiple levels of detail tables under a main table, a detail table can appear under only one main table. Lookup tables can appear under multiple main tables.

A main table cannot be a detail table in a different hierarchy at the same time. In addition, a child table cannot have more than one parent. In other words, hierarchies cannot embed other hierarchies, and they cannot share common tables. Hierarchies define a partition over user tables.

Detail tables can have their own child tables with column entries in the modColumns table pointing to the detail tables. This makes consistent representation for data hierarchies possible. It also makes it possible for each main table to have more than one detail table and hierarchies of any depth.

You can apply table-level permissions uniformly to all the tables in the hierarchy. However, users can change the permissions on each table. Because detail tables are similar to rows (or sets of rows) in the virtual schema of the hierarchy, setting table permissions amounts to setting permissions on columns or groups of columns.

You can define main-detail dependencies in a hierarchy by associating one row from the main table with one row from the detail table. Such a relationship can be based on a primary key/foreign key constraint, but this is not a requirement. For example, you can associate the City column in a Customer table with a list of postal codes in a detail table. If many customers live in the same city, they will get the same list of postal codes. This is a many-to-many relationship and is valid in project hierarchies.

A workflow application can have other primary key/foreign key relationships defined between its tables. For example, two main tables, Customer and Orders, can have a one-to-many relationship defined on the customer ID row. Those relationships are valid in a workflow application, but they have no role in Workflow Designer for SQL Server-specific functions. In the example, the Customers and Orders tables can have their own workflow tables with no implicit dependencies on each other. The row-level permissions set on the Customers table will not be propagated to the Orders table and vice versa. The primary key/foreign key relationship, however, can be used in the data access pages designer to build hierarchical reports based on Customers and Orders.

The relationships created by the hierarchical arrangement of the tables are stored in two tables: modObjects and modColumns. The modObjects table stores information about user-defined tables and their relationships, as well as SQL views, user views, triggers, and stored procedures. The modColumns table stores entries for selected columns in a workflow application that require additional properties.

See Also

Setting up a Table Hierarchy | Defining Table Security | Creating a Table Hierarchy | Adding Detail and Lookup Tables | Removing User Tables