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:
- Open the Business Object Properties window:
- 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.
- 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.
- Select Bus Ob Properties.
- In the
CSM Administrator main window, select the
Blueprints category, and then select the
Create a New Blueprint task.
- Select the Database page.
- 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.
- 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).
- 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:
- Select
Add Index.
An expanded view of the new index opens.
- 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.
- Define the Business Object Fields that contain the values for
the index in the database table:
- Select
Add to select Fields to include in
the database index.
The Add Field to Index window opens.
- Select a Field.
- 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.
- 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.
Select OK.
- Select
Add to select Fields to include in
the database index.
- Select
Add Index.
- Select the
down arrows to expand an existing index.
From an expanded index, you can:
- Edit the index.
- Delete the index.
- Add Index: Select this button to
add an index.
-
Select OK.
- Publish the Blueprint ( ) to commit the changes, or save the Blueprint ( ) to continue making other changes.