Working with Data Sources and Database Schemas

Methods and options when working with Data Sources and Database Schemas are described in this topic.

What is the best way to create a Database Schema?

You can create a Database Schema by using the Database Wizard, or do so independently with the Schema editor. The wizard is recommended, as it gives you more flexibility in selecting only the tables you require, as opposed to populating all tables and rows, and then going through additional pruning (i.e., removing unwanted tables) when not using the wizard. More importantly, the wizard automatically creates and helps you configure the other collaborating objects needed for your database integration projects – the Data Source and Database Adapter.

How do I add tables to an existing Database Schema?

While the Schema editor allows you to delete tables directly from a Database Schema, it does not allow you to add them. There are different ways to edit an existing Database Schema to add tables or other changes. Consider the following options.

Option 1: Use the Database Refresh Wizard to identify changes to your back-end database

When a table or record has been added to a back-end database, use the Refresh Wizard to identify the change, and then apply that change to the Data Source, Database Adapter, and any Database Schema that is referencing the Data Source. This is the recommend method.

Option 2: Use the Populate from Data Source button in Database Schema editor

From the Database Schema editor, use the Populate Schema button  to populate the Schema contents from a Data Source, but keep in mind that this will overwrite the existing Schema contents, with the exception of implied decimals (warning message shown below).

This provides a simple albeit global method to ensure that any new additions (as captured in the Data Source) are reflected in the Database Schema. It should be noted however that since all of the tables existing in the selected Data Source will be added to the Database Schema, you would need to select only the tables needed, build them into the hierarchy, and then delete the rest.

How do I apply database deletions to my Database Schema?

To delete a table or column directly from a Database Schema, take these steps:

1. Open the Database Schema editor.
2. Locate the table or column to be deleted.
3. With the cell selected, click Remove  (right-clicking also provides this option, shown below).
4. Save and close.

How do I update my Data Source?

CIC Studio gives you two options to update a Data Source based on changes to a back-end database.

1. Use the Database Refresh Wizard (Recommended).
   • Select the Data Source to be updated.
   • Select tables and columns to be refreshed.
   • Apply refreshed tables and columns.

   Part of this process also lets you update any related Database Adapters or Schemas – a major advantage of using the Database Refresh Wizard.

2. From directly within the Data Source editor, use the Fetch Metadata button to synchronize the Data Source with the assigned Database. You'll be prompted with the following warning before you can proceed:

   

Note: Changes to the Data Source can impact any other Database Adapter or Database Schema referencing it; additional modifications to these objects will be required.