Define Database Properties for a Business Object

Use the Database page in the Business Object Properties window to define how to set up the database table that represents a Business Object. This includes record update options, indexing, and setting a primary key.

Good to know:

• All Business Objects are stored as database tables in SQL Server. Group Objects are stored in one table (example: The Customer table also includes all of its Group Members), except for linked External Business Objects.
• Encrypted Fields are not stored in database tables, and cannot be indexed.

To define database properties for a Business Object:

1. Open the Business Object Properties window:
   1. In the CSM Administrator main window, select the Blueprints category, and then select the Create a New Blueprint task.

      The Blueprint Editor opens, showing the Object Manager in its Main Pane. The Object Manager lists the existing Business Objects.

   2. In the Object Manager, select a Business Object in the Object tree, and then select the Edit Business Object task in the Structure area.

      The Business Object Editor opens.

   3. Select Bus Ob Properties.
2. Select the Database page.
3. Define general database properties:
   • Custom Storage Name: This is the name that will be used for the database table that represents the Business Object. The Custom Storage Name is automatically populated with the Business Object's Internal Name defined in general properties (modified as necessary to be a legal SQL table name).
     Note: For best results, do not change the Custom Storage Name unless you have a specific reason. If the name is changed, it must adhere to SQL Table naming conventions (example: Automatically be derived from the display name, not use invalid values such as spaces, etc.).

   • Pessimistic Updates: Select this check box to enable pessimistic updates when Business Object records are saved. This prevents Users from simultaneously editing records and overwriting each other's updates.
     Note: Normally, the system uses optimistic updates for saving Business Object records. With optimistic updates, if two Users edit the same record, and then both save the record, the changes from the last User to save the record will overwrite the changes made by the first User. If pessimistic updates are enabled, the last User to save a record will receive a message that the record cannot be saved because another User has just edited the record. In most cases, using record locking is a better option.

4. Create indexes for Fields that you expect Users to search frequently to speed up searching and record retrieval. The system automatically creates indexes for values that it typically looks for (example: RecID), but it is recommended that you create indexes for the Field that holds the Public ID of an Object and for any other Fields you expect Users to search. The system automatically creates an index for the Public ID field if you selected to create one when you defined the Field (on the General page).
   • Add Index: Select this button to add an index.

     To add an index:

     1. Select Add Index.

        An expanded view of the new index opens.

     2. Define index attributes:
        • Name: Provide a name for the index.
        • Primary Key: Select this box to make the index the table key. This is automatically selected for the index on RecID (which is automatically created when you create a Business Object).
        • Clustered Index: Select this box to use the index to physically order the rows in the database table. This is automatically selected for the index on RecID (which is automatically created when you create a Business Object).

          Note: Each Business Object (table) can have only one primary key and one clustered index. If you designate more than one index as the primary key or clustered index, you will receive an error when you select OK in the Business Object Properties window. By default, the index for the RecID field is designated as the primary key and the clustered index. If you assigned a RecID attribute to a different field, then the index for the field with the RecID attribute should be the primary key and clustered index.

        • Unique Index: Select this box if each of the columns in the index must have a unique set of values so that multiple rows in the database cannot be identical. For example, if the columns in the index are First Name and Last Name, there cannot be two rows in the table with the values John Smith.
     3. Define the Business Object Fields that contain the values for the index in the database table:
        1. Select Add to select Fields to include in the database index.

           The Add Field to Index window opens.

        2. Select a Field.
        3. Define a sort order for the Field values:
           • Select the Ascending radio button to sort the values for the Field in ascending order.
           • Select the Descending radio button to sort the values for the Field in descending order.
        4. Select Include Column to include the Field value in the index. This is an advanced optimization feature that can be used to include additional columns needed by queries without pulling the data from the actual table, while keeping the index key small and efficient.
           Note: Click the Delete button to delete a selected Field from the index. Use the arrows to change the order of the Fields in the index. When building indexes, the order of columns is important. Typically, the column that most limits the results should be the first column in the index. However, following this guideline might require many additional indexes to achieve required performance from the database, as each index requires SQL to perform additional work for each insert, update, and delete. In such cases, it is advisable to put columns used by many different queries first in an index.

           Note: Include columns do not have to be ordered.

        5. Select OK.

   • Select the down arrows to expand an existing index. From an expanded index, you can:
     • Edit the index.
     • Delete the index.

5. Select OK.

6. Publish the Blueprint (File > Publish) to commit the changes, or save the Blueprint (File > Save) to continue making other changes.