On 12/10/14 3:07 PM, Monnier, Gary wrote:
Regardless of what "protein", "starch" & "vegetable" represent you
still have a header/detail situation.
That part was obvious. The "protein" (of which there is exactly one in
the "meal") is in the header record, in the header file. Each "starch"
in a "meal" (of which there can be zero or more) is a detail record, in
one detail file. Likewise, each "vegetable" (of which there can be zero
or more) is detail record, in another detail file. These detail files
would of course contain other things besides the name of the "vegetable"
or "starch."
Likewise, the desired user interface is well-defined: once the "protein"
has been selected from a list of all the available "proteins," the user
can add "starch" and "vegetable" detail records to it, for which we
present the him/her with a subsetted list of acceptable "starches," and
a subsetted list of acceptable "vegetables," for the selected "protein."
The problem at hand is generating the subsetted lists. The assumption is
that every "starch" and every "vegetable" goes with at least one
"protein," but it's entirely possible that a given "starch" or
"vegetable" might go with only one "protein," or two, or ten, or a
hundred, or all of them, or anything in between.
On the one extreme, the hierarchical lookup table idea I was presented
with is conceptually simple, but would require a record for every valid
combination of "starch" and "protein." Meaning massive redundancy, and
meaning that if this file can't be generated programmatically, it could
add up to thousands, if not tens of thousands, of records worth of data
entry, and lots of places to make mistakes.
On the other extreme, either of the two bitmapped schemes would result
in lookup tables with zero redundancy, but it would also mean that if
the total number of "starches" and/or the total number of "vegetables"
were to grow beyond any expansion space built into bitmaps in the
"protein look-up" table, or the number of "proteins" were to grow beyond
any expansion space built into bitmaps in the "starch look-up" or
"vegetable look-up" tables, then we need to modify the affected look-up
table structure to expand the overfilled bitmap. And even if they never
needed more bitmap capacity, they still might need specialized
maintenance programs: without something to break down the bitmaps, it
could be like hand-counting the cards from the infamous "butterfly
ballot" system.
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.
Having a list of relevant "protein" codes (or the special value of
"ANY") in each record of the "starch" and "vegetable" look-up tables,
duplicating the "starch" and "vegetable" records where we run out of
room, would also be a middle ground, and it looks rather promising to
me, but it would require us to know more about how long those "protein"
lists should be, in order to minimize the need to "double up" (or
"triple up," or more).
Of course, it's even possible that the customer in question might be in
a position to relieve me of this whole piece of the puzzle, by giving me
"factory" programs that would, given a "protein" code, vend the list of
relevant "starches" and "vegetables." Probably a long shot, though.
--
JHHL
midrange.com
