Database Schema Design Guidelines

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 basing a workflow application on an existing database, remember:

• Before registering an existing database as a workflow application, make a backup copy of the database.
• Do not attempt design changes on a production database. Move or copy your database to a test environment, and perform all workflow implementation and schema changes there. After you are sure the workflow application is functioning as expected, deploy it to your production server.

When creating a database that you plan to incorporate into a workflow application, there are certain schema limitations with which you should be familiar before you start development. These restrictions also should be reviewed carefully if you plan to add workflow to an existing Microsoft® SQL Server™ database.

General Guidelines

• SQL Server makes it possible to use the following data types in single column primary keys: datetime, smalldatetime, float, real, decimal, money, smallmoney, int, smallint, tinyint, timestamp, nvarchar, nchar, varchar, char, varbinary, binary, and uniqueidentifier.

• SQL Server does not make it possible to use the following data types in primary/unique keys: text, ntext, image, and bit.

• Table relationships are supported in Workflow Designer table hierarchies if they are based on primary key/foreign key relationships where those keys are any keys supported by SQL Server (including multi-column keys).

• Row-level security only can be implemented on a main table if it has a single column primary/unique key and that column is of type tinyint, smallint, or int.

• When you add a main table to a table hierarchy in the Workflow Designer, views are created for that main table and any related detail tables. If these tables already have permissions set on them, you must set the permissions on the associated views manually. Workflow Designer for SQL Server does not replicate existing table permissions to the newly created views.

  Note   Unrelated and lookup tables do not have views created for them.

• If you create a view in your database after the database has been enabled for workflow, you must apply permissions manually on the newly created view. If this view is to form the basis for a data access page, you must grant select privileges on the primary key and insert and delete privileges on the other columns to the roles that will be using the data access page.

• When naming objects, do not use spaces or special characters such as: " / \ [ ] : ; | = , + * ? < >. Also, avoid using SQL Server keywords, because the name will be interpreted as a keyword and not a database name.

Workflow Schema Guidelines

You only can enable workflow on tables with a single-column primary key or unique key. You cannot enable workflow on tables with concatenated keys (more than one column making up the primary key).

Row-Level Permissions Schema Guidelines

• To enable row-level permissions, a table should have an integer-compatible column that can be used as a row identifier by the code that enforces row permissions. Integer compatible types are int, smallint, tinyint, numeric, and decimal.

  Workflow Designer for SQL Server looks first for an int primary key and then for an identity column. The rowID column is provided by the modGetPermissionsJoinColumn stored procedure.

See Also

Workflow Application Development Guidelines | Workflow Scripting Guidelines | Data Access Page (User Interface) Guidelines