Master/detail relationships are omnipresent in business applications. From department/employees to country/cities, data is often categorized and presented with this one-to-many type of relationship. With the interactive grid, a new component in Oracle Application Express 5.1, you can now easily create editable grids that reflect master/detail relationships. And you can easily extend your application to represent any number of relationships—parent/child/grandchild—or even a master table and multiple detail tables.
In this Oracle Magazine article, you’re going to build a web application on top of a set of three tables: REGIONS, COUNTRIES, and LOCATIONS. COUNTRIES is a child table of REGIONS, and LOCATIONS is a child table of COUNTRIES. These tables are excerpted from the Oracle Database Sample Schemas, available on GitHub. Using Oracle Application Express 5.1, you will quickly create a single page to manage the data of all these tables, using interactive grids.
This article’s sample application is built in Oracle Application Express 5.1. If you’re not already running Oracle Application Express 5.1 or later locally, you can request a free workspace at apex.oracle.com. Alternatively, you can download from the Oracle Technology Network the Database App Development Virtual Machine, which includes a preconfigured Oracle Database 12c Release 2 Enterprise Edition database, Oracle Application Express 5.1, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler. You will also need to download and unzip the SQL script for this article to create the sample database objects.Creating the Sample Database Objects
Begin your exploration of Oracle Application Express 5.1 interactive grids by first creating the sample database objects of your application.
You’ve now created the REGIONS, COUNTRIES, and LOCATIONS tables in your schema and populated them with data.Creating the Application
Begin your exploration of master/detail support in Oracle Application Express 5.1 by creating the initial application.
Data Managementfor Name, and click Next.
You’ve now created a responsive Oracle Application Express application with a fully functional, multiuser editable grid on the REGIONS table, along with a child editable grid on the COUNTRIES table, all accessible from a web browser.
Click the Run Application icon to run your application. Log in with the same credentials you used to log in to Oracle Application Express. Click each of the rows in the Region Name column of the Regions interactive grid to refresh the child interactive grid with the related rows. Enter text in the search field of the Regions and Countries interactive grids, and press the Enter key to filter the results.
Your application should look similar to Figure 1.
Figure 1: The initial master/detail application page
Improve the display of the page by refining the layout and visual appearance of the interactive grid regions.
With very wide columns and only one or two columns displayed per interactive grid, a significant amount of space on the application page is wasted. You can easily adjust the properties of the grid regions to dramatically improve the display.
Your application page now presents the interactive grid regions side by side, with the Save button in the breadcrumbs bar. You’re getting there, but this application can be improved further.
Template options are declarative modifiers for the display of content on a page. The new live template options feature in Oracle Application Express 5.1 enables you to modify the look and feel of your content and see the results in real time.
The functionality of the interactive grids in your application page remains as before, but the presentation of information is much more condensed on the page. Your application should look similar to Figure 2.
Figure 2: The master/detail application page, condensed
Oracle Application Express supports an unlimited number of interactive grids on a single page. You will now add a third interactive grid, this time for the LOCATIONS table, and then manually adjust the properties so it is associated as a child of the COUNTRIES table.
select location_id, street_address, postal_code, city, state_province, country_code from locations
Your application should look similar to Figure 3. You now have a page with three editable grids and can freely add, update, and delete rows in each of them. But the last remaining step for building this application is to specify the master/detail relationship between the Countries interactive grid and the Locations interactive grid.
Figure 3: The master/detail application with three editable grids
In the updated application page, click various rows in the Regions interactive grid. Select a row in the Countries interactive grid, and use the arrow keys on your keyboard to navigate up and down the list of rows. You will see the rows in the Locations interactive grid automatically refresh.Conclusion
The new interactive grid component in Oracle Application Express 5.1 makes it very easy to represent a variety of data relationships in a responsive web application. With very little code, you are able to create editable grids directly on top of your tables, preserving the parent/child/grandchild relationships. Although there are alternative ways to present tables with master/detail relationships in Oracle Application Express, multiple interactive grids on a single page will be very recognizable to users of legacy client/server applications. A future Oracle Magazine article will discuss other powerful interactive grid features in depth.
READ more about Oracle Application Express.
Photography by Ricardo Gomez, Unsplash