Database Maintenance Models

It impressed me that Paul Holm was able to deploy several database inquiry
and maintenance screens in a matter of hours using his framework.  We need
to be able to do that.  Our RPG models are getting close.  But our Servlet
development is trailing.  We're doing both types of development.

We've committed to replacing several hundred 5250 database maintenance
programs, among other things.  The UI requirements for "basic inquiry and
maintenance" are somewhat different, and perhaps a little more robust than
the screens Paul generated.  It may be helpful to illustrate and describe
them.

http://www.relational-data.com/etl/search.jpg


Let's focus on basic database inquiry and maintenance, although the screen
shot also shows part of the Portal we're developing in connection with the
project.  Incidentally, we reviewed the Portal with representatives from
about 25 school districts this week in a conference setting; some of the
members of the consortium who are funding the project.  They gave us an
enthusiastic thumbs up!

The Portal provides a common runtime environment for deploying Servlets,
5250 programs, RPG based Web, and other applications.  When users activate
Servlets from the menu, the Portal links to the Servlet's entry point and
passes a key value, enabling the Servlet to query a file containing
information such as the district, school, and runtime parameters pertaining
to the user.  For example, the Servlet may need to retrieve the current
Library List, file overrides, or a number of other parameters pertaining to
the user.

The menu bar shown in dark blue, and the space above it, is managed by the
Portal, while the space below would be managed by the active program.

Our model for basic inquiry and maintenance begins with a screen listing
records from the table or view of interest, selected from the menu.  Above
the record list, in the light blue shaded area, is where default record
selection criteria might be keyed.  A combo box lists default Order By
options. The Advanced Search link activates a popup showing a fairly robust
English-like dialog for dynamically generating more complex SQL select
statements tailored to the table or view of interest.  The dialog would be
comparable to popular query tools, but restricted to the table or view at
hand.

The number of rows and pages meeting the selection criteria are shown.
Users have the option of changing the number of rows per page, adjusting the
size of the list according to monitor resolution.  When a different page
size is selected, the total page count is adjusted accordingly.  The current
page is shown.

A Position To box, offers a binary search of the selected records, according
to the Order By clause.  The label of the textbox changes when the user
changes the sort order.  The Position To operation doesn't perform another
query, it just positions to a row in the current record set.

VCR style Links enable paging (First, Prior, Next, Last).  A combo box
enables navigation to specific pages.  A checkbox adjacent to every record
in the list could be clicked to select records to work with.  Or, hyperlinks
enable the selection of a page at a time, or the selection of all records in
the set.  If the record set continued say 150,000 records and the user
clicked Select All, followed by Delete, then 150,000 records would be
deleted.  We may need to secure that ;-).

Since the server is showing just a page at a time, the checkbox state needs
to be maintained on the server, and dynamically reflected as the user pages
back and forth through the list.

One thing missing from this model, which I think is needed, which Paul
Holm's framework has, is a way to dump the record set to an Excel
spreadsheet, or comparable file, that users could download and work with on
their local workstation.

Action icons to the right of records enable users to View, Change, Copy, or
Delete individual records.  Or, corresponding hyperlinks enable the same
operations for the set of selected records.  An example of a basic
maintenance screen is shown as follows:

http://www.relational-data.com/etl/detail.jpg


The detail screen is divided into left and right-hand panels.  The left-hand
panel shows selected record(s) from the previous screen.  An arrow indicates
the currently selected record.  Record navigation is performed by clicking a
record in the left-hand panel, or using the VCR links in the right-hand
panel.

When working with individual records, a combo box indicates the current mode
(view, add, copy, change, or delete), which also enables users to change
from one mode to another.  Add mode is a little different.  The record list
shows records added instead of records selected from the previous screen,
the mode box and VCR links would be disabled.  However, clicking a record in
the list, would set the screen to Change mode.

An image adjacent to a field could be clicked to activate a popup, listing
possible values for certain fields.  Fields in error would have a red
background, with a generic error message displayed at the top of the panel,
and specific error text could be shown by hovering the mouse over the field
in error.  That's about it.  What are we missing?  Keep in mind, this would
be just for basic file inquiry and maintenance.  We know we'll need to
extend the model for Header-Line activities, such a purchase order
maintenance.  Even so, just the basic model would probably apply to several
hundred tables.

Nathan Andelin.