Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Pizza Example

Re: Pizza Example

From: Jerry Gitomer <jgitomer_at_erols.com>
Date: Sun, 04 Apr 2004 11:59:19 -0400
Message-Id: <pan.2004.04.04.15.58.34.760791@erols.com>


On Sat, 03 Apr 2004 15:41:05 -0600, Dawn M. Wolthuis wrote:

> In trying to get a better handle on relational database design compared to
> XML or PICK database design, I'm writing up the example below and got
> stuck on just how a relational theorist would likely design this. Below
> is the design for a non-1NF implementation. I'll use my terminology for
> this so that it is not confused with relational terms.
>
> Function/File: ORDER_ITEMS
> Purpose: This file maps the OrderID | ItemNumber to details of the ordered
> item
>
> Fields and sample data:
> Key:
> OrderID.................72819
> ItemNumber...........1
> OrderedItem..............Pizza
> Size...........................16"
> Crust.........................Chicago Sauce........................Tomato
> Cheese......................Mozzarella
> .................................Feta
> .................................Parmesan
> Topping....................Pepperoni
> ................................Black Olives
>
> Requirements for an application using this data include displaying line
> items for the information in a particular format. I realize this is not
> relevant for relational data design, but gets to the heart of what I'm
> trying to understand with the relational model.
>
>
>
> Order Item OrderItem Size Crust Sauce Cheese Topping
>
> 72819 1 Pizza 16" Chicago Tomato Mozzarella
> Pepperoni
>
>
> Feta Black Olives
>
>
> Parmesan
>
>
>
> This is, not coincidentally, how a non-1NF language, such as the PICK
> query language (which goes by many names) might show the data with a
> statement such as:
>
>
>
> LIST ORDER_ITEMS OrderID ItemNumber OrderedItem Size Crust Sauce Cheese
> Topping
>
>
>
> (LIST is like SQL SELECT but with the function/file typed first, then
> the fields)
>
>
>
> In addition to the ORDER_ITEMS file, I would also have a file that
> includes valid entries, along with descriptions with a file such as:
>
>
>
> Function/File: VALIDATIONS
>
> Purpose: This file maps a validation list name to the set of valid
> entries and their descriptions
>
>
>
> Fields & sample data:
>
> Key:
>
> ValidationList.............Cheeses (with other validation lists in
> this
> same file for validating Sauces, Crusts, Toppings, and OrderedItems,
> having those values as the key for the relevant validation list)
>
> ValidEntries (designed with the below fields associated with each
> other):
>
> Abbreviation................Mozzarella
>
> .................................Feta
>
> .................................Parmesan
>
> Description..................Mozzarella Cheese
>
> .................................Feta Cheese
>
> .................................Parmesan Cheese
>
>
>
> Then to show the descriptions instead of the abbreviation, the LIST
> statement would include a virtual field CheeseDesc (for example) instead
> of the stored data from the field named "Cheese". In other words, the
> "vocabulary" for the OrderItems function would be expanded to
> incorporate these descriptions as virtual fields.
>
>
>
> How would you model this data for an RDBMS and what would an analogous
> SQL statement look like, both for just the stored order data and if
> there is a join to get the descriptions for cheeses, for example? I
> realize there would be many more tables than there are files but it is
> not clear to me just how this would typically be designed and queried.
>
>
>
> Thanks in advance for your help. --dawn

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 - 10:59:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US