Re: Database design issue: seems really bad, yet I'm having trouble coming up with anything better

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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.