On 12/10/2014 7:56 PM, James H. H. Lampert wrote:
I'm looking for something that avoids the massive, error-prone
redundancy of hierarchical, and yet also avoids the maintenance
nightmare of a bitmapped system.
Matt Olson's idea of having a mapping file to tie each "starch" to the
"proteins" to which it is relevant, and the same for "vegetables," might
work, but I'm thinking it might be a bottleneck, and if we were to do
the mapping file within the existing framework we would be using for the
header and detail records would create massive overhead.
I can't speak to either massive or error prone, but we've settled on
cross reference tables to handle our many to many relationships as we
slowly modernise our database and associated applications. We have many
tens of thousands of rows in cross reference tables using surrogate keys
and are very happy with the performance (it's all SQL access) and
flexibility. End users are very pleased that they can finally be in
charge of their own data without having to call a programmer in so often.
Searching and subsetting make end users very happy. For those
accustomed to typing in customer numbers and product codes, those are
part of the search criteria, which is generally a keyword search. Break
the name / description into individual tokens, add the 'natural' key
like the customer number and tie it to the surrogate key of the row.
Use one search field and let the keyword index cover all the columns
they're likely to want to search.
For the maintenance interface, allow the ability to create a new menu
based on the one they see on the screen right now. Take them
immediately to the maintenance panel with the new menu populated. It'll
reduce their data entry work significantly. While you're at it, allow
them a 'copy from menu' capability so that when they're maintaining
ground beef and their colleague tells them that it's close to ground
pork. They hit the F key, search ground pork, X the items that they
want to copy and they love it. Give them as many ways to look at their
data as you can think of.
With respect to performance, the width of the many to many cross
reference is sort of a non issue. That there are create / update time
and user stamps is irrelevant because the SQL SELECT statement doesn't
reference them except for the maintenance programs. I have strong
opinions on performance problems based on many years of working on
undersized hardware.
The first rule of RLA performance is to measure it and THEN conclude
what the performance IS. Do not conjecture what it /might be/ - IBM i
can do some amazing things with single level store. If, after
measuring, you conclude you have a bottleneck, create logical files that
only have the cross reference fields and not the 'housekeeping' fields.
You've now reduced your I/O channel usage by that much as well as the
time that RPG to map the buffer to RPG variable names.
The first rule of SQL performance is to measure and then ask the
optimiser what indexes it would like. Then create them - typically, no
application change is required. The optimiser does its thing and the
application gets zippy all by itself.
I AM sorry for the long post. I'm as concise as I think I can be.
These are opinions, informed by more than 3 decades of RPG programming.
The many to many tables are working great for us, and I thought you'd
like to hear that.
midrange.com
