MES database schema

This data reference contains a detailed description of all tables, fields and relationships in the current version of the Manufacturing Execution System (MES) database model. The descriptions are grouped in related sections and the sections are arranged roughly in the recommended order of configuration. The information provided in this document is for understanding the MES database model. Direct modification of any of the records in these tables is not supported by AVEVA. All interaction must be through the various client applications which make API calls through the MES Middleware or through calling the various MES APIs.

Database Tables

In each section the relevant tables are described with an ER diagram and then the details of each table are presented in tabular format, including all field names, data types, primary and foreign keys, validation rules, etc.

In the detailed tabular format the PK column indicates Primary Key and the Nulls column indicates whether Null data is permitted for that field or not.

Indexes, Foreign Keys, and Unique Keys

Beneath the schema of each table are lists of indexes (some, not all), foreign keys (FK), and/or unique keys (UK), i.e., unique constraints. There are three kinds of foreign keys, which are shown in parentheses behind the description of the key fields and the table to whose primary key (PK) they relate.

• Inhibit delete means that the main record cannot be deleted as long as a dependent record that references it exists.

• Cascade delete means that the key is defined so that when the main record is deleted, all dependent foreign records are also deleted. In some cases, this cannot be made a property of the foreign key itself, because doing so would cause cycling problems. Instead, it is accomplished by having any stored procedure that would delete the main record first delete the dependent records. In these cases, the words implemented by SP are added.

• These first two types are used where the dependent record has no meaning or worth without the main record. Set null indicates that the foreign key relationship is set up as it would be for "Inhibit delete", but any stored procedure that would delete the main record first sets the foreign key fields of the dependent table to NULL. This is used where the absence of such a value does not render the dependent record useless, but where the foreign key values are not meaningful without the main record.

Validation Rules and Default Values

Any validation rules to be performed by the database are detailed for each field together with a default value in cases for which a default can be assigned to a non-nullable field. (Nullable fields always default to null unless specified otherwise). These defaults are applied when rows are added to the respective tables and certain fields are omitted from the Add function. Fields whose description contains the word enumeration are constrained by the database to have only the values referenced in the description.

Tracking Edits

All tables that are changeable by a user include the following fields:

last_edit_by

The ID of the user who created or last changed the record.

This field is not nullable for tables that were created with that field initially, but is nullable for tables that existed initially without it, as there is no reasonable default value for it to assign to existing records.

last_edit_at

The UTC date and time of its creation or last change.

This field is never nullable. For those tables that did not have it prior to version 2.1, it was set by the 2.0 to 2.1 conversion script to the current UTC date and time for any existing records. Values of existing last_edit_at fields were changed by that conversion from local time to UTC.

last_edit_comment

Reserved for internal use to indicate why this record was changed.

In a few tables, equivalent fields exist with different names (e.g., last_modified) and are used instead of these fields.

Last_edit_at and last_edit_by are maintained automatically by the stored procedures that insert or update records having these fields. They may be retrieved but not set by the stored procedures. These fields are mainly used for passing information to the audit trail triggers.

Concurrency Issues

For some tables for which there might be concurrency issues due to more than one user inserting or editing data at the same time, a mod_id field is included. (The mod_id is a binary number that increments each time the row is modified.) This allows for Optimistic Concurrency by not locking the database unnecessarily for updates, but instead checking before updating that the current row has not been updated since this user last retrieved the data. This means updates occur on a first-come-first-served basis, and subsequent updates get refused until the user re-retrieves the current data and re-issues the update statement. If a table does not have a mod_id field, its last_edit_at field can be used in the same way.

The mod_id field datatype, timestamp, is a misnomer in that it is not a date/time value. As a SQL datatype, timestamp has been deprecated and replaced by the rowversion datatype.