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 data sets that have at least one common field with 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. To resolve data from multiple data sets, refer to "Resolving data from multiple tables".

The master and detail data sets do not have to be of the same data set type. For example, you could use a QueryDataSet as the master data set and a TableDataSet as the detail data set. QueryDataSet, TableDataSet, and DataSetView can all be used as either master or detail data sets.

These are the topics covered:

Defining a master-detail relationship

When defining a master-detail relationship, you must link columns of the same data type. For example, if the data in the master data set is of type INT, the data in the detail data set must be of type INT as well. If the data in the detail data set were of type LONG, either no matches or incorrect matches would be found. The names of the columns may be different. You are not restricted to linking on columns that have indexes on the server.

You can sort information in the master data set with no restrictions. Linking between a master and a detail data set uses the same mechanism as maintaining sorted views, a maintained index. This means that a detail data set will always sort with the detail linking columns as the left-most sort columns. Additional sorting criteria must be compatible with the detail linking columns. To be compatible, the sort descriptor cannot include any detail linking columns or, if it does include detail linking columns, they must be specified in the same order in both the detail linking columns and the sort descriptor. If any detail linking columns are included in the sort descriptor, all of them should be specified.

You can filter the data in the master data set, the detail data set, or in both. A master-detail relationship alone is very much like a filter on the detail data set; however, a filter can be used in addition to the master-detail relationship on either data set.

Instead of using a MasterLinkDescriptor, you may use a SQL JOIN statement to create a master-detail relationship. A SQL JOIN is a relational operator that produces a single table from two tables, based on a comparison of particular column values (join columns) in each of the data sets. The result is a single data set containing rows formed by the concatenation of the rows in the two data sets wherever the values of the join columns compare. However, for JOIN queries to be updateable with JBuilder, changes must be restricted to only one of the tables and you must override the default update mode.

Fetching details

In a master-detail relationship, the values in the master fields determine which detail records will display. The records for the detail data set can be fetched all at once or can be fetched for a particular master when needed (when the master record is visited).

Be careful when using the cascadeUpdates and cascadeDelete options for master-detail relationships. When using these options, one row of a detail data set may be updated or deleted, but the others may not be. For example, an event handler for the editListener's deleting event may allow deletion of some detail rows and block deletion of others. In the case of cascaded updates, you may end up with orphan details if some rows in a detail set can be updated and others cannot.

Fetching all details at once

When the fetchAsNeeded parameter is false (or Delay Fetch Of Detail Records Until Needed is unchecked in the masterLinkDescriptor dialog box), all of the detail data is fetched at once. Use this setting when your detail data set is fairly small. You are viewing a snapshot of your data when you use this setting, which will give you the most consistent view of your data. When the refresh() method is called, all of the detail sets are refreshed at once.

For example, initially the data set is populated with all of the detail data set data. When the fetchAsNeeded option is set to false, you could instantiate a DataSetView component, view the detail data set through it, and see that all of the records for detail data set are present, but are being filtered from view based on the linking information being provided from the master data set.

Fetching selected detail records on demand

When the fetchAsNeeded parameter is true (or Delay Fetch Of Detail Records Until Needed is checked in the masterLinkDescriptor dialog box), the detail records are fetched on demand and stored in the detail data set. This type of master-detail relationship is really a parameterized query where the values in the master fields determine which detail records will display. You are most likely to use this option if your remote database table is very large, in order to improve performance (not all of the data set will reside in memory - it will be loaded as needed). You would also use this option if you are not interested in most of the detail data. The data that you view will be fresher and more current, but not be as consistent a snapshot of your data as when the fetchAsNeeded parameter is false. You will fetch one set of detail records at one point in time, it will be cached in memory, then you will fetch another set of detail records and it will be cached in memory. In the meantime, the first set of detail records may have changed in the remote database table, but you will not see the change until you refresh the details. When the refresh() method is called, only the current detail sets are refreshed.

For example, initially, the detail data set is empty. When you access a master record, for example Jones, all of the detail records for Jones are fetched. When you access another master record, say Cohen, all of the detail records for Cohen are fetched and appended to the detail data set. If you instantiate a DataSetView component to view the detail data set, all records for both Jones and Cohen are in the detail data set, but not any records for any other name.

When the fetchAsNeeded property is true, there should be a WHERE clause that defines the relationship of the detail columns in the current QueryDataSet to a parameter that represents the value of a column in the master data set. If the parameterized query has named parameter markers, the name must match a name in the master data set. If "?" JDBC parameter markers are used, the detail link columns are bound to the parameter markers from left to right as defined in the masterLink property. The binding of the parameter values is implicit when the master navigates to a row for the first time. The query will be re-executed to fetch each new detail group. If there is no WHERE clause, JBuilder throws DataSetException.NO_WHERE_CLAUSE. When fetching is handled this way, if no explicit transactions are active, the detail groups will be fetched in separate transactions. For more information on master-detail relationships within parameterized queries, see Parameterized queries in master-detail relationships.

When the the master data set has two or more detail data sets associated with it, and the fetchAsNeeded property of each is true, the details remember what detail groups they have attempted to fetch via a query or stored procedure that is parameterized on the active master row linking columns. This memory can be cleared by calling the StorageDataSet.empty() method. There is no memory for masterLink properties that do not set fetchAsNeeded to true.

When the detail data set is a TableDataSet, the fetchAsNeeded parameter is ignored and all data is fetched at once.

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 GridControl, 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.

Steps to creating a master-detail relationship

To create a master-detail link between two data set components, one which represents the master data set and another which represents the detail data set,

  1. Skip this step if you currently have an application set up with at least two data set components, one of which represents the master data set and another which represents the detail data set. If you would like to set up an application but have not yet done so, see Querying a database for more information. For a tutorial on creating a master-detail relationship using the sample database files shipped with JBuilder, see Tutorial: Creating a master-detail relationship.

  2. Select the detail data set and select the masterLink property from the Properties page of the Inspector. In the masterLink custom property editor, specify the following properties for the detail data set:

  3. Add two visual controls (such as grids) to enable you to view and modify data. Set the dataSet property of one to the master data set, and set the dataSet property of the other to the detail data set.

  4. Compile and run the application. The master data set will display all records. The detail data set will display the records that match the values in the linked columns of the current row of the master data set, but will not display the linked columns.

To save changes back to the tables, see Saving changes in a master-detail relationship.

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 employee.gdb, and one that selects all of the employees. This tutorial is available as a finished project in the samples/borland/samples/tutorial/dataset/MasterDetail directory of your JBuilder installation under the project name MasterDetail.jpr.

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 grids and as you navigate through the COUNTRY grid, the EMPLOYEE grid displays all of the employees who live in the country indicated as the current record.

To create this application,

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

  2. Select Frame1.java in the Structure pane and then select the Design tab of the Content pane. Add a Database component from the Data Express tab to the Component tree. Set the connection property for the Database component as follows, assuming your system is set up as in Installing and setting up:

    Property name Value
    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    Click the Test Connection button to check that the connection properties have been correctly set. Results of the connection attempt are displayed in the gray area below the button. When the gray area beneath the button indicates Success, click OK to close the dialog.

  3. Add a QueryDataSet component from the Data Express tab to set up the query for the master data set. Set the query properties of the QueryDataSet component from the Inspector as follows:
    Property name Value
    Database database1
    SQL Statement select * from COUNTRY
    Place SQL Text In Resource Bundle uncheck

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

  4. Add another QueryDataSet component from the Data Express tab to the Component tree. Select the query property. 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
    Place SQL Text In Resource Bundle uncheck

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

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

  6. Add two GridControl components from the JBCL tab to the UI Designer. Bind the data sets to the grids by setting the first GridControl's dataSet property to queryDataSet1 and setting the second GridControl's dataSet property to queryDataSet2.

  7. Compile and run the application by selecting Run|Run.

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. The running application looks like this:

Next, you might want to modify rows of data, sort information in either data set, select which columns to include in each data set, create a lookup list, create a calculated column, locate data, create a data module to encapsulate this relationship for re-use in other applications, or save changes back to the data source. See other topics in this book for information on these activities.