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

On 12/10/14 10:13 AM, Monnier, Gary wrote:

For your scenario:

Two tables to define the meal..
One table for the meal (header) the protein/non-protein (must be in the proteins master table).
- Tofu is a protein albeit not meat of any sort.
- The non-protein entry in the protein master may be described as desired: maybe as Air or Protein-Free?
One table for the addition dishes for the meal (detail)
- dish type (starch, vegetable, etc.)
- dish ID (ID must be in the corresponding master table)

Keep in mind that the food analogy here is exactly that: an analogy. We're not really talking about food here, except as a metaphor for the real world situation (which I'm not at liberty to discuss). We can assume that, for the sake of the analogy, the protein file has non-meat records including (but not limited to) "tofu," "TVP," "Scrambled eggs," "overripe Limburger," "peanut butter," and "nothing at all."

And indeed, the food analogy breaks down on the conceit that only certain starches and vegetables are compatible with any given protein. If I want to have poultry dressing, Yorkshire pudding, and carrot mousse with Spam piccata, it may be a weird combination, but it's certainly possible. In the real world scenario, we can assume that dressing ONLY goes with poultry dishes, and Yorkshire pudding ONLY goes with roast beef.

Now, the obvious answer to me, if everything were closed-ended, would be either (1) to have a bitmap (or more likely byte-map) field in the starch and vegetable fields, with one element for every protein, indicating compatibility, or (2) to have two bitmaps in the protein file, indicating the compatible starches and vegetables. The problem with this is that it's inherently closed-ended, and even if some expansion space were built into the bitmaps, it would be necessary to change the structure if that expansion space were exceeded.

--
JHHL