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.
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,
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.
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.
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.
To create a UI for this application,
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.
Among other information, the status bar displays information about the current record or current operation.
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.
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".
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.