Pizza Example

From: Dawn M. Wolthuis <dwolt_at_tincat-group.com>
Date: Sat, 3 Apr 2004 15:41:05 -0600
Message-ID: <c4nb1k$22h$1_at_news.netins.net>

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 Received on Sat Apr 03 2004 - 23:41:05 CEST