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, including:

• Update options: Whether to enable pessimistic updates (Business Object record cannot be saved if another User has just edited it).
• Indexes: Which Fields in the Business Object to use to create indexes (expedites searching and record retrieval).
• Primary key: Which index to use as the table key (typically RecID).

Good to know:

• The Business Object Properties window is available in the Business Object Editor (accessed from within the Object Manager in a Blueprint).
• All Business Objects are stored as database tables in SQL Server. Group Objects are stored in one table (ex: 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, click the Blueprints category, and then click the Create a New Blueprint task.

      Note: If working on a saved Blueprint, open the existing Blueprint.

      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, click a Business Object in the Object tree, and then click the Edit Business Object task in the Structure area.

      The Business Object Editor opens.

      Tip: You can also click the Edit Business Object button on the Blueprint Editor Toolbar to open the Business Object Editor.

   3. Click the Bus Ob Properties button.
2. Click 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: We recommend that you 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 (ex: 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. Define indexes for the Business Object. Indexes speed up searching and record retrieval.

   Tip: Create indexes for Fields that you expect Users to search frequently. The system automatically creates indexes for values that it typically looks for (ex: 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: Click this button to add an index.

     To add an index:

     1. Click 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 click 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. Click Add to select Fields to include in the database index.

           The Add Field to Index window opens.

        2. Click a Field to select it.
        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.

           Tip: Click the Delete button to delete a selected Field from the index. Use the Up/down 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. Click OK.

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

5. Click OK.

6. Publish the Blueprint (File>Publish Blueprint) to commit the changes, or save the Blueprint (File>Save Blueprint) to continue making other changes.
   Note: Before publishing a Blueprint that contains Business Objects, ensure that each Business Object has at least one Form and one Grid defined and that all of the appropriate Fields have been created.