Solution Database Tables

See Also

A solution database contains information about one particular team solution. It holds all the data entered by users and enforces rules and actions on data processing. For solutions created using a template, the template defines the solution's structure and its associated components.

A solution database is self-contained. It holds all the system information and database tables necessary to run the team solution. The entire solution database or a subset can be replicated for offline use by end users. For information, see Offline Replication and Allowing Users to Work Offline.

The tables within a solution database fall into two categories: system tables and user tables. For more information about the relationship between user tables, see Team Solutions Table Hierarchies.

Table type Description
System tables Store information about the schema or structure of the solution (modObjects, modColumns, and modPermissions); a type of system table used internally to implement workflow rules, lookups, and so on.
User tables Hold the data for the solution.

Table Naming Conventions

Important   When naming your solution database and your database tables, do not use special keywords or the names of system or reserved 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.

System tables   Each solution has system tables with predefined names: modObjects, modColumns, modPermissions, modProperties, modObjectTypes, and system replication tables. 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 Access Workflow Designer 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 tables   Table names are user-defined and should conform to the syntax for SQL identifiers. Access Workflow Designer supports both quoted and unquoted identifiers. The length of an SQL identifier is limited to 128 characters.

The following is an example of the types of solution tables in a typical team solution. For more information about these tables, refer to the Issue Tracking solution.

Table Table type Description
modColumns system table Stores the solution columns and their properties.
modObjects system table Stores the solution 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 solution.
modProperties system table Defines name/value pairs for the solution database and is used to store information about the offline replication settings for the solution.
modPublications user table Stores information required to define offline publications.
modPubObjects user table Stores the objects that must be replicated for each offline publication.
modReplIncludeList user table Stores a list of the objects that should be included in a publication.
modSubscriptions user table Stores information required to create subscriptions.
modSubObjects user table Stores the objects that have been replicated to each subscriber.
modUserRoles user table Associates user names with solution roles.
modWebFiles user table Stores Web site URLs for team Web pages that should be available offline.
Issues user table (main) Holds the root rows of each issue. The schema is defined by the solution developer and can be customized.
Comments user table (detail) Holds the comment rows for each issue. The schema is defined by the solution 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 solution.
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 solution.
IssuesAuthorLookup lookup table Stores keywords for the Author column in the Issues table. This table is created when you add workflow to your solution.
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 solution.