 |
||
REPRESENTING AND FORMATTING COMPLEX DATA STRUCTURES
Introducing The Restaurant Menu Problem
Occasionally someone will ask me how they might include records from other tables in the review pages generated by Red Queen. By other tables I mean tables created and managed by Red Queen, but not normally inspected by Red Queen during the usual page generation process. In this tutorial I'll explain how this can be done, using the example of the restaurant menu as a complex data structure that can be stored as a series of linked tables, and formatted using the Template Toolkit.
I should point out here that there is nothing about the restaurant menu that limits the nature of this discussion about complex data structures. It just happens to be a good non-trivial example. So if you are looking to add auxiliary data to your Red Queen pages, try to think about how your data might be represented using the methods discussed here. There is a good chance you can do exactly the thing you are hoping to.
As is usually the case, I probably would not have looked at this problem had someone not expressed a serious need to include data that could not be handled using the (until then) existing database mechanisms that Red Queen employs. In this case a webmaster wanted to add restaurant menus to his review pages. More specifically, these menus needed to go into the Supplier Profile pages, and could be fairly lengthy.
To get a feel for the kind of data involved, have a look at this real life example of a formatted restaurant menu for San Luigi's of New York City.
If you inspect the menu you'll see that structurally it consists of a small set of meal types (typically Breakfast, Lunch, Dinner) and each of these is broken down sections (appetizers, entrees, etc) and finally, each section contains a number of different selections (actual foods/beverages, complete with descriptions and prices).
Representing The Menu As Normalized Tables
You cannot easily use a single table to represent this kind of nested data. For example, if you are using the Supplier table to represent restaurants, you cannot add lots of excess columns to accomodate the different components of the menu. You simply do not know ahead of time how many columns will be required. Adding and removing data would also be awkward. The thing to do, of course, is to make use of the relational nature of SQL tables to capture the relation between the data components. Instead of using one table to represent the data, we should use several. For example: a Menu table, a Meal table, a Section table, and a Selection table.
I like to name my related tables similarly, so from here on I will refer to the following four related menu tables: the Menu table, the MenuMeal table, the MenuSection table, and the MenuSelection table. For these four tables here is a representation in term of possible columns:
Table Column ----- ------ Menu id name MenuMeal id menu_id name start_time end_time MenuSection id meal_id name description MenuSelection id section_id name description price
Each of these tables contains an PRIMARY KEY column (the INTEGER id column). Each table, other than the Menu table at the top, also has a foreign key column, the value of which associates a record in that table to the table above it. So, for example, the MenuMeal.menu_id column is an INTEGER column that holds the value for the PRIMARY KEY of the Menu record with which it is associated. So, if we start at the bottom table, the value of the MenuSelection.section_id column will tell us which record in the MenuSection table should be looked up. When we know the value of the MenuSection.meal_id column we can lookup the related record in the MenuMeal table. Finally, the value of the MenuMeal.menu_id column gives us the Menu record.
Notice that we have to start at the bottom of the table hierarchy and work our way up. This is because every record is associated with exactly one record above it. We cannot move unambiguously in the other direction, from top to bottom, because at each level any given record may be associated with several below it (for example, a "Beverages" record in the MenuSection table might have half a dozen different beverages associated with it in the MenuSelection table).
Any number of other columns can also be added at each level to properly represent the records. For example, at the bottom most level, where the menu selections are itemized, the description and price of the selection are important pieces of information for the customer hoping to make a decision about what to order.
If you are familiar with database theory you will recognize the table structure above as a form of data normalization. Each core characteristic of the data has been allocated its own table.
Another thing to keep in mind as you follow this discussion about the representation of the restaurant menu as a series of nested tables, is that the structure of your own data need not be as complex as that of the restaurant menu. You may not need 4 tables, or even 2. You might only want to retrieve a single record from a single table and put it on your review pages, or perhaps into the Supplier or Member Profile page. For example, if all you wanted to do was retrieve information from the Menu table, you could do that too, without extracting any lower-level information. The discussion applies equally well to that case.
Next Section: FORMALIZING THE TABLE RELATIONS
Documentation
Documentation for Red Queen, which is entirely separate from these tutorial pages, is an ongoing project (as is this tutorial). You should find that there is sufficient information in the Red Queen User Manual to answer most of your technical needs. Suggestions for improvement to both the User Manual and this tutorial are always welcomed. As more users gain experience with Red Queen, and issues are resolved for its application in different areas, information gained from that experience will make its way into these pages.
Note: If you cannot find the information you are looking for in this tutorial, try the Red Queen User Manual which you should find far more comprehensive than the instructional dialog found here.
Copyright © 2004 Random Mouse Software. All Rights Reserved.