Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Grandville MI

    Recursive relations & modeling real world objects

    Dear Somebody,

    Two months ago I posted a note regarding the design of a database that
    I'm modeling for a deli. One gentleman wrote and told me to try this:

    CUSTOMER orders FOOD
    FOOD makes up ORDER

    This worked well for simple food-stuffs like Sandwiches, Salads and
    Soups but I have to be concerned with objects like combo meals and box
    lunches whom are a collection of discrete food stuff, ie: (combo is a
    1/2 sandwich with soup or salad). I came up with an ER diagram Please see attached. Below is a description:

    There are four entities (customers, orders, food_items and combo_meal).
    There are six relations that tie those objects together;

    order_has_comb (attribs are qty)
    combo_has_sandwich (attribs are qty and bread)
    combo_has_salad (attribs are qty and dressing)
    combo_has_soup. (attribs are qty)

    A combo_meal must contain one 1/2 of a sandwich and either a soup or a
    salad. An order must have an customer but not vice/versa. A order must
    have a food item but food item can exist if its not in a order.

    Now, the reason for this posting is a concern over the "comb_meal"
    entity. I noticed that after I did a ER to Relation mapping, I wrote my select statments and never used the comb_meal table! It seems to exist only to relate an order to a combo_meal and a
    combo_meal to the items in the combo_meal. Any infomation I could want regarding combo meals I can get from any of the other relations; order_has_comb, combo_has_sandwich, combo_has_salad & combo_has_soup. Could this be bad design? Can you have an entity that exists only to relate two other relations? (Please see attached ER diagram.) I am a novice and realize that my inexperience could doom my efforts. Appreciate any feedback.

    NOTE: So that it's clear to everyone reading this my Business Requirement for this project is to design a database that allows cutomers to order their meals from a web application. Nothing more nothing less. No order tracking, no keeping track of re-order levels. Nothing like that. I just want them to be order Sandwiches, Salads, Soups, Drinks, Chips seperatly or bundled as a combo_meal or box lunch.

    PS. I said recursive because the only time I ever saw a ER model that
    showed how to relate items to items in one entity was where someone modled a parts entity to a assembled-parts entity and showed it in a ER diagram as a recursive relationship, (i did not do this).

    Many thanks

    Brian J Stoffer ( or
    Attached Thumbnails Attached Thumbnails trythis.png  

  2. #2
    Join Date
    Dec 2003
    There is nothing wrong with your design.

    Sell all sandwiches as halves.

    Your combo table should be mentioned in your where clauses.
    Last edited by certus; 05-19-04 at 23:47.

  3. #3
    Join Date
    Jan 2004
    Grandville MI

    price of combo

    Thank you very much Certus for the reply. This makes me feel a whold lot more comfortable. If you don't mind I would like to ask you one more question regarding the design. looking over my description you might have noticed that my relations (combo_has_sand, combo_has_salad, combo_has_soup) have the following attributes (qty, bread_type, dressing_type). The combo_meal entity only has a primary key of combo#. Logicaly I should make the 'price' 0f the combo meal an attribute of combo_meal but that would be redundant since their is only one price for a combo meal.

    My solution is to insert a row in the food_items table (which is a table that contains descriptions of all food items, example theirs a row for each type of sandwich (turkey salad, turkey ham...)) for a combo meal whos columns would be, 'description' (combo meal), 'type' (meal) and price ($5.35).

    Does this violate the "one fact one place" rule? bad design?

    Can't thank you enough

    Brian J Stoffer

    PS. I'll go back over my select statments to make sure that they are joined with the combo_meal table. This confuses me a little but I think I understand why.

  4. #4
    Join Date
    Dec 2003
    You have a small structure You can afford to expand it.

    Here's a very rough model. All I had handy was Visio.
    Attached Thumbnails Attached Thumbnails order.gif  
    Last edited by certus; 05-20-04 at 16:59.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts