Updating the database design

About this task

The database design comprises more than its entity and link types, it also includes items that affect its usability, such as datasheets, and supporting files. The items to consider fall into these categories:

• Database schema, such as link types and code lists

• Folder objects, such as queries and report definitions; and non-folder objects, such as datasheets and labeling schemes

• Other files, which are database-specific but not part of the database, such as supporting files for report definitions

Print a database design report to obtain a list of items to review in your database.

Note: the report does not include items such as folder objects, or datasheets and charting schemes.

After replication is configured, iBase provides tools to update the database design at all sites. Nevertheless, it can take time to make these changes, mainly because of the cooperation that is required between sites. You must also require the cooperation of the SQL Server administrators at each site as they must delete and then re-create the publication and subscriptions.

Items that must exist before the publisher site is configured for replication

The database schema must be set up correctly before replication is configured for the publisher site. For database replication, the items in the database schema are:

• Entity types

• Link types

• Fields

• Standard fields

• Pick lists, icon lists, and SCC lists (but not their contents, which can be changed by any authorized user at any time)

• Chart attributes

• Datasheets

• Common folder objects (folder objects that are marked as common folder objects in iBase but not dependent on any data)

• Semantic types and the way they are assigned

You might want to make certain changes to the database schema to support database replication. Specifically, adding a Merge Request entity to help manage the merging of records, and adding owner fields to reduce the risk that conflicts might occur.

The following objects are not replicated, and need to be added to the database template before you set up new databases at the subscriber sites:

• Labeling schemes

Public and private folder object control groups are not replicated but the objects they contain are copied for all users. (Folder object control groups are defined in the security file.)

Note: You might want to delete unwanted objects at this stage otherwise they are copied to the databases at the subscriber sites.

Items that can be added at any time

The items in pick lists, icon lists, and SCC lists can be updated at any time as the contents of these lists are replicated.

The following non-database files can also be replicated (by loading them in the File Manager) and therefore do not need to be considered when you prepare the database for replication. The exact list of files depend on how you use iBase:

• Command groups (CommandGroups.mdb)

• Word documents that are used for styles or as templates in reporting

• Lists of excluded words for search indexes (WSexclude.txt)

• Icon lists (Iconlist.txt) and, if customized, the associated graphic files (.ico)

• i2 Analyst's Notebook templates

• Mapping configuration files

If there are many files, for example Word documents, then consider archiving them in a .zip file before you load them in the File Manager.

Note: After replication, you will need to copy these files to the correct place in the database folder or to the correct folder in the iBase installation.

Items that are site-specific

The Word Search and Full-Text Search indexes must be built individually at each subscriber site.

Possible schema changes for replication

When you review the current database schema, you might want to consider making the following two changes to support iBase database replication.

Adding a Merge Request entity type

The Merge Request entity type is intended to support the procedure for merging entities. A procedure is required because the iBase Conflict Viewer does not handle conflicts that arise from merging entities or occurring in the records that are affected by a merge operation.

When you design a suitable entity type for Merge Requests, you might want to add fields for:

• The name and location of the analyst

• The reason for the merge

• Details of the records for the proposed merge, for example by listing the record IDs.

• A screen capture of the pre-merge data

• Room for an analyst from each site to enter comments

• Voting buttons, one for each site (you might use a checkbox or a list with Yes/No/Discuss options)

For more information, see Merging Entities.  

Adding owner hyperlink fields to entity and link types

To reduce the likelihood that conflicts occur, you can define a hyperlink field that automatically inserts the name of the current user whenever a new record is saved. Users can see this information when you show or edit the record. For more information, see Checking the Ownership of Records.

To define this type of field, use iBase Designer to create new fields or a new standard field with:

• The hyperlink field type

• A name such as Owner

• A default value of $ (the $ symbol automatically inserts the name of the current user when the record is saved)

You must also update your datasheets to show the owner field.

Process for changing the design of a live database

To make changes to the design of a live database that is not yet configured for replication in SQL Server, work on an isolated copy of the iBase database.

After you finalize the design, you can apply the changes to the live database using the Update Database Schema dialog (you can use this dialog even though replication is not yet configured) or you can make them by hand.

The steps are:

1. Create a template from your live database.

2. Create a temporary database from the template, in either Microsoft Access or SQL Server format.

3. Make the required changes to the design of your temporary database, considering all the issues described in this topic.

4. Test the new design in iBase.

5. Create a template to capture the new design. This template is needed by the subscriber sites.

You can then repeat these changes in the live database or use the Update Database Schema dialog to apply some of the changes for you. Changes to folder objects and datasheets must always be made manually.  

Procedure

Apply the updated design to a live database:

1. Create a template from the temporary database that contains the updated database design.

2. Create another copy of the live database for test purposes. The database must be:

   • SQL Server format, with the database properties listed in To create a subscription database.

   • Initialized for replication. For details, see Initializing the database for replication.

3. Apply the updated database schema to the test database:

   1. In iBase, log on as a database administrator and open the database.

   2. Load the database template into the database using the File Manager: select Tools > Replication File Manager. See Replicating non-database files for details.

   3. To prevent users from opening the database (and to inform users that the database is closing), select Tools > Replication Take Offline, and then click OK. See Shutting Down the Databases for details.

   4. Select Tools > Replication Update Database Schema.

   5. Click Update to apply the changes shown in the Update Database Schema dialog and then, after this is finished, click OK to reopen the database.

   6. Select Tools > Replication Bring Online.

4. Check that the template was applied successfully.

5. If the template was applied successfully, apply the changes to the live database.

What to do next

Creating a database template

For details of how to create and test a database template, see Updating the database design. The databases at all sites must have the same database design. After you finalize the design of the iBase database, and completed the preparation of the database: