Re: Pizza Example

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.

	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

	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

	Item		Attribute/Description
	7		F	
	7		G
	7		H
	7		I

	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.

	72819  20.00  1.20  Dawn
	72820  12.00   .72  Jerry

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 - 17:59:19 CEST