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

On Thu, Dec 11, 2014 at 9:57 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

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

This is actually tantamount to the thing he has been calling the
"hierarchical solution" that was already presented to him before even
starting this thread. It's the very thing that he is looking to avoid
because it *feels* too bulky and redundant to him.

I agree with you, though. It's the natural way to store these
relationships in a database.

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.

He already finds devoting AN ENTIRE RECORD to a single relationship
far too bulky. No way keeping records for combinations that are
invalid/unrelated would fly. Nor should it; that's just crazy talk.
(Ever heard of "combinatorial explosions"?)

John Y.