Re: Pizza Example
From: Alan <alan_at_erols.com>
Date: Mon, 5 Apr 2004 10:23:10 -0400
Message-ID: <c4rq4f$2m2i4g$1_at_ID-114862.news.uni-berlin.de>
> > 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 Mon Apr 05 2004 - 16:23:10 CEST
Date: Mon, 5 Apr 2004 10:23:10 -0400
Message-ID: <c4rq4f$2m2i4g$1_at_ID-114862.news.uni-berlin.de>
Jerry,
You forgot onions and Cheese Whiz for the cheese steak.
Anyway, I generally try to explain to implementers that implementation is not the time to design a database. Start with the business requirements, create an ERD, and then create the relational schema. So, I would not go from the requirements directly to the tables- at least not in theory. It's a great way to miss cases of specialization, etc.
-Alan. Yes, I'm still there.
"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:
>
> > 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 Mon Apr 05 2004 - 16:23:10 CEST