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;

customer_has_order
order_has_customer
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 (brianstoffer@hotmail.com) or brians@dnx.net

There is nothing wrong with your design.

Sell all sandwiches as halves.

Your combo table should be mentioned in your where clauses.

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.

You have a small structure You can afford to expand it.

Here's a very rough model. All I had handy was Visio.