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

James,

It sounds like a standard associative entity (ie. junction or map) to me
used to store a many to many relationship.
https://en.wikipedia.org/wiki/Many-to-many_(data_model)

There usually isn't much too them compared to the master tables whose
relationship they store.

There really isn't a better way to handle them in the DB. I'd used Matt's
model, except that there wouldn't be a ProteinStarchMapID or
ProteinVegtableMapID. I'd use a composite PK made up of the two FK to the
corresponding two master tables.

ProteinStarchMap File:
ProteinID, StarchID

ProteinVegtableMap File:
ProteinID, VegtableID


Personally, I'd store it sparsely. The existence of a record means the
starch/vegetable is compatible. Otherwise it is not. Alternatively you
could add a compatible flag and store every possible combination.

From a maintenance standpoint, it's easy select a given protein and

generate a list of all possible starches (from the Starch Master) with a
flag showing rather or not a record exists in ProteinStarchMap. The uses
just goes through and checks or unchecks the boxes.

Assuming you have need of a "Weekly Menu" that stores the schedule of
selected meals. The Menu (or MenuMap) table would simply be
ScheduledDate
ProteinID
StarchID
VegtableID

Using the associative tables, it's real easy to make sure only valid meals
are stored by defining a FK constraint...
foriegn key (proteinID, starchID) references ProteinStarchMap (proteinID,
starchID)
foriegn key (proteinID, vegtableID) references ProteinVegtableMap
(proteinID, vegtableID)

Lastly, have you ever looked at Object Role Modeling?
http://www.ormfoundation.org/

I've found it useful for DB design & validation. Particularly when
interacting with the non-technical.



On Wed, Dec 10, 2014 at 9:09 PM, James H. H. Lampert <
jamesl@xxxxxxxxxxxxxxxxx> wrote:

On 12/10/14 5:27 PM, Evan Harris wrote:

A mapping file seems the logical way to go about it, and it seems like it
would naturally lend itself to some kind of caching approach, which might
just overcome your bottleneck concerns.

The problem with the mapping file approach is, as I said, the overhead if
it's done within the application's framework: its record structures have,
before a single user-defined field is added:

Fields for creation date, creation time, and creation user
Fields for last-update date, time, user, job, and program
An automatically generated ID field
A flag field.

That totals 86 bytes of overhead, per record, in addition to whatever
overhead is inherent to DB2/400.

The "protein" code is 3 characters. The "vegetable" or "starch" code is 8.

86 bytes of overhead for 11 bytes of mapping data, in a file that would
have to have a separate record for each valid combination of "protein" and
"vegetable," and the same, in another file, for each valid combination of
"protein" and "starch." Something about that strikes me as being not much
of an improvement over the initial hierarchical solution that seemed so
wrong.

Putting space for up to 10 "protein" codes, space-delimited, in each
"starch" and "vegetable" record, would add 39 to 41 characters (depending
on whether we need leading and/or trailing spaces for the entire field) to
each "starch" and "vegetable" record. With 20 "protein" codes per record,
it would be 79-81 characters. Then, the search would work out to nothing
more than, say, looking for all "starch" records for which the "protein
list" field contains the code for the desired "protein."


--
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.