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 team solution database typically includes a combination of main tables, detail tables, and additional lookup tables. As you develop your solution, you can specify additional main tables to provide a variety of offline publications or workflow processes with your team solution.

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

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

Detail user tables**** are child tables in the hierarchy. 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 solution is a detail table that holds comments associated with each issue in the Issues table.

   Lookup tables are helper tables in the hierarchy. These tables are used by Access Workflow Designer to manage core features, such as offline and workflow. Lookup tables have a many-to-one relationship with other tables in the solution. For example, the IssuesWorkflow table in the Issue Tracking solution is a lookup table that stores all the states of the workflow process for the Issues table.

For information about adding tables to your solution hierarchy, see Creating a Table Hierarchy.

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 solution hierarchies.

A team solution 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 team solution, but they have no role in Access Workflow Designer-specific functions. In the example, Customers and Orders 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 the parent information for each table, and the modColumns table stores the related columns.

For more information, see Database Tools and Technologies, Creating a Table Hierarchy, and Defining Table Security.