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