Re: Pizza Example

Very helpful -- just the kind of solution I was looking for, Jerry. Thanks! --dawn

"Jerry Gitomer" <jgitomer_at_erols.com> wrote in message news:pan.2004.04.04.15.58.34.760791_at_erols.com...
> On Sat, 03 Apr 2004 15:41:05 -0600, Dawn M. Wolthuis wrote:
<snip>
Dawn,
> You are describing a common order entry application. The traditional way
> this is handled in an RDBMS is to create three tables:
>
> 1. Item Table - one row for each item that can be ordered.
> 2. Components Table - one row for each component (see below)
> 3. Link Table - one row for each valid Item to Customization link.
>
> Characteristics such as size of the pizza will be included as part of the
> description in the Item Table. Using your example the item description
> could be 16" Chicago Pizza. While a real item table will contain more
> than just a description I am omitting any additonal information in the
> interests of clarity. Our table may look like:
>
> Key Description
> 1 16" Chicago Pizza
> 2 12" Chicago Pizza
> 3 16" Sicilian Pizza
> 4 12" Sicilian Pizza
> 5 12" Philly Cheese Steak
> 6 6" Philly Cheese Steak
> 7 Jumbo Burger
> 8 Junior Burger
>
> Each row in the Components Table will consist of an Attribute
> Column and a Description Column. For example;
>
> Key Attribute Description
> A Cheese Mozzarella
> B Cheese Feta
> C Sauce Tomato
> D Topping Mushrooms
> E Topping Pepperoni
> F Sauce Ketchup
> G Sauce Mustard
> H Trimmings Lettuce
> I Trimmings Tomato
>
> The Link Table exists to express the one-to-many relationship between the
> item ordered and its components. The validation is in effect built into
> the system through the use of the Link Table, since it permits only
> "legal" combinations to be created. For example the Link Table rows
> describing the valid components for a Jumbo Burger are:
>
> Item Attribute/Description
> 7 F
> 7 G
> 7 H
> 7 I
>
> Note that I can add or delete items and components at will and that my
> data structure will easily accomodate new items and new components.
>
> SELECT item.description, component.description
> FROM item, component, link
> WHERE link.item = item.key
> AND link.attribute = component.key
> GROUP BY item.description
>
> To satisfy your requirement for tracking individual orders I wold add
> three more tables.
>
> 4. Order Table - one row per order.
> 5. Order-Items - one row per item ordered.
> 6. Order-Item-Components Table - one row per order-item component.
>
> Each row in the Order Table would include identifying information, Total,
> Sales Tax, Customer Name, etc. For exanple:
>
> 72819 20.00 1.20 Dawn
> 72820 12.00 .72 Jerry
>
> Each row in the Order-Items Table would include the Order #, qty, item #,
> and the price.
>
> Each row in the Order-Item_Conents Table would include the Order #, qty,
> item #, component #, and any component surcharge.
>
> Processing an order will require access to all six tables, but if all of
> the tables are properly indexed (yes, I know indexes are part of the
> physical implementation and not part of the RDBMS, but this is one of the
> differences between theory and practice) the system response times will
> seem instantaneous to the users.
>
> HTH
> Jerry
Received on Sun Apr 04 2004 - 19:18:42 CEST