Establishing a master-detail relationship

Databases that are efficiently designed include multiple tables. The goal of table design is to store all the information you need in an accessible, efficient manner. Therefore, you want to break down a database into tables that identify the separate entities (such as persons, places, and things) and activities (such as events, transactions, and other occurrences) important to your application. To better define your tables, you need to identify and understand how they relate to each other. Creating several small tables and linking them together reduces the amount of redundant data, which in turn reduces potential errors and makes updating information easier.

In JBuilder, you can join, or link, two or more data sets that have at least one common field using a MasterLinkDescriptor. A master-detail relationship is usually a one-to-many type relationship among data sets. For example, say you have a data set of customers and a data set of orders placed by these customers, where customer number is a common field in each. You can create a master-detail relationship that will enable you to navigate through the customer data set and have the detail data set display only the records for orders placed by the customer who is exposed in the current record.

You can link one master data set to several detail data sets, linking on the same field or on different fields. You can also create a master-detail relationship that cascades to a one-to-many-to-many type relationship. Many-to-one or one-to-one relationships can be handled within a master-detail context, but these kinds of relationships would be better handled through the use of lookup fields, in order to view all of the data as part of one data set. For information on saving changes to data from multiple data sets, see the "Resolving data from multiple tables" topic.

Tutorial: Creating a master-detail relationship

This tutorial shows how to create a master-detail relationship, using the sample files shipped with JBuilder. The basic scenario for the sample application involves constructing two queries, one that selects all of the unique countries from the COUNTRY table in the employee sample file, and one that selects all of the employees.

The COUNTRY data set is the master data set, with the column COUNTRY being the field that we will link to EMPLOYEE, the detail data set. Both data sets are bound to JdbTables, and as you navigate through the COUNTRY table, the EMPLOYEE table displays all of the employees who live in the country indicated as the current record.

To create this application,

  1. Select File|Close. Select File|New, and double-click the Application icon. Accept all defaults.

  2. Select the Design tab of the Content pane. Select a Database component from the Data Express tab of the component palette, click in the Structure pane to add the component to your application. Open the connection property for the Database component in the Inspector, and set properties as follows, assuming your system is set up to use the JDataStore sample as described in "Installing and setting up JBuilder for database applications" (which includes adding the JDataStore library to the project):

    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL jdbc:borland:dslocal:/usr/local/jbuilder/samples/JDataStore/datastores/employee.jds

    (you can browse to this file to reduce the chance of making a typing error, click the Browse button)

    Username Enter your name
    Password <not required>

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed beside the button. When the connection is successful, click OK.

    The code generated by the designer for this step can be viewed by selecting the Source tab and looking for the ConnectionDescriptor code. Click the Design tab to continue.

  3. Select a QueryDataSet component from the Data Express tab, and click in the Structure pane to add the component to your application. This component sets up the query for the master data set. Select the query property of the QueryDataSet component from the Inspector, and set as follows:
    Property name Value
    Database database1
    SQL Statement select * from COUNTRY

    Click the Test Query button to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  4. Add another QueryDataSet component from the Data Express tab to the Structure pane. Select its query property in the Inspector. This will set up the query for the detail data set. In the query custom property editor, set the following properties:
    Property name Value
    Database database1
    SQL Statement select * from EMPLOYEE

    Click the Test Query button to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  5. Select the masterLink property for the detail data set (queryDataSet2) in the Inspector. In the masterLink property editor, set the properties as follows:

  6. Select a DBDisposeMonitor component from the "more dbSwing" tab. Click in the Structure pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

  7. Set the DBDisposeMonitor's dataAwareComponentContainer property to this.

To create a UI for this application,

  1. Select a JdbNavToolBar component from the dbSwing tab. Drop the component in the area slightly above the panel in the Design frame. JdbNavToolBar automatically attaches itself to whichever DataSet has focus.

    This will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.

  2. Click on the JdbStatusLabel component on the dbSwing tab of the component palette. Drop the component in the area slightly below the panel in the Design pane. JdbStatusLabel automatically attaches itself to whichever DataSet has focus.

    Among other information, the status bar displays information about the current record or current operation.

  3. Select contentPane in the Structure pane. Set its layout property to null.

  4. Select a TableScrollPane component from the dbSwing tab, click and drag the outline for the pane in the upper portion of the Design pane to add it to the application.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  5. Select a JdbTable component from the dbSwing tab, click inside the TableScrollPane component to add it to the pane. Set its dataSet property to queryDataSet1.

  6. Add another TableScrollPane to the lower part of the Design window.

  7. Add a JdbTable component to the lower TableScrollPane. Set its dataSet property to queryDataSet2.

  8. Compile and run the application by selecting Run|Run Project.

Now you can move through the master (COUNTRY) records and watch the detail (EMPLOYEE) records change to reflect only those employees in the current country. For more information on saving changes to data sets that are linked, see "Saving changes in a master-detail relationship".

Editing data in master-detail data sets

You cannot delete or change a value in a master link column (a column that is linked to a detail data set) if the master record has detail records associated with it.

By default, detail link columns will not be displayed in a JdbTable UI component, because these columns duplicate the values in the master link columns, which are displayed. When a new row is inserted into the detail data set, JBuilder will insert the matching values in the non-displayed fields.