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.
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:
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 is implemented by 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 updatable with JBuilder, changes must be restricted to only one of the tables and you must override the default update mode.
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).
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.
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 and 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.
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 resolve or cancel all changes and re-fetch the details.
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 detail data set is a TableDataSet, the fetchAsNeeded parameter is ignored and all data is fetched at once.
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.
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,
Running
, Success
, or Failed
.
To save changes back to the tables, see Saving changes in 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,
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.
This concludes the master-detail tutorial. 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 also:
Common database application tasks
Saving changes in a master-detail relationship
Working with columns
Encapsulating your data module for re-use
Sorting data
Locating data
Using calculated columns
Creating lookups