I think the header-detail model will satisfy your requirements. Between them you can build all possible variations. My first suggestion did assume there is only 1 protein per meal. If there are multiple proteins for a meal as well as multiple other dishes, move protein to the detail table.
Load the variations into whatever structure you feel is appropriate for a given situation: Maybe you want to use Linq to combine header & detail, maybe you want to do record I/O against the header & detail tables, maybe you want to use an SQL join view to combine header & detail, maybe you want to build your own mapping technique to combine header & detail.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James H. H. Lampert
Sent: Wednesday, December 10, 2014 10:35 AM
To: Midrange Systems Technical Discussion
Subject: 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
--
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.
midrange.com
