 |
||
FORMALIZING THE TABLE RELATIONS
Adding Foreign Keys To A Table
In the previous section we saw that the way to connect records in one table with those in another is to do so by adding foreign key columns: the value of a FOREIGN KEY specifies the value of the PRIMARY KEY in another table, to which the record is related. Example: the MenuMeal.menu_id column is a foreign key to the Menu table - if it has the value 3 we know that it is related to the Menu table record for which the Menu.id column has the value 3.
We know this, because we set up the columns and named them in such a way that the correspondence is obvious. But to clue Red Queen in on this we need to formalize the relationship. We can do this by using the Relation control panel in Red Queen.
When you visit the Relation control panel you can view existing table relations or add new ones. Do not edit any existing relations in case you destroy Red Queen functionality. The only relations you should edit are the ones you have created yourself. In a moment we are going to add foreign key relations to our set of nested tables.
I am supposing at this point that you have already created your set of nested tables using the Red Queen Database control panel. By setting tables up this way you create an internal table definition file that Red Queen can refer to when performing table operations. When we add foreign key relations we are simply editing those table definition files to add formal relationships.
You need to be aware of 3 things when creating your nested tables:
(a) Your PRIMARY KEY in each table should be an INTEGER column
(preferably an AUTO_INCREMENT column to make life easier when adding records).
(b) You should add no more than ONE foreign key relation to each table. That way
there is no ambiguity when the time comes to looking up foreign keys and relating
each table to the one above it.
(c) Name you
With your nested tables constructed, visit the Relation control panel and click on the Create link in the left side column. This will bring up a form to add a new relation, which will look something like this (shown in the filled out form):
|
|
The form above shows you how to add a foreign key to the MenuMeal table, where the foreign key specifies the PRIMARY KEY value for the Menu table, which is called the Primary Table in the Relation control panel terminology. The table we are adding the foreign key to is called the Related table here.
Once the form for the first part of the process has been submitted, you will see other:
And that's it. Click Save and your new foreign key relation is set up connecting the MenuMeal table to the Menu table. You will have to repeat that process for the other table pairs too. In this case, for the MenuMeal / MenuSection pair and the MenuSection / MenuSelection pair. This completes the formal relations for the set of nested tables.
Aren't We Missing Something?
If you are keen-eyed you will have noticed that we said nothing about the relationship between the Menu table and any of the thing tables in your Red Queen database (that is, the Item, Member, or Supplier tables). Clearly we need to connect those too. Let's say we want to connect Menu records to the Supplier table. Here we have a choice of either adding a supplier_id foreign key to the Menu table, or adding a menu_id foreign key to the Supplier table.
Which is better? It's a toss up. If each Menu record can belong to no more than one Supplier record, maybe adding a Menu.supplier_id column would be the right thing to do. But we are not going to do that. Instead we'll add a Supplier.menu_id column as the foreign key. This has 2 minor advantages. We are always going to be accessing the Supplier record anyway, so if we don't find a value for the Supplier.menu_id column we won't have to do a menu lookup. Secondly, we spend most of our time in Red Queen looking at thing records anyway, so having the foreign key in the thing table allows us to see at a glance what the associated menu is for each record.
So, to be clear, the last relation you add will look like this on the Relation form:
|
|
In the next section we will see how to instruct Red Queen to extract ALL the relevant nested records needed to construct a full menu.
Extracting Nested Data
Now that we have set up foreign keys on the nested tables, it is time to tell Red Queen to interpret these foreign key relations in the context of nested table data. That is, we want the program to automtically extract the data needed to create a fully formed menu.
To do this, go to the Configure > Nested Tables configuration page and add a set of nested tables. The form will look something like this: (shown in the filled out state):
| Add NEW Nested Table Relation | |||
| To create a new nested table relation, fill in the form below. If your selection appears to be valid it will be added to the existing set of nested table relations. | |||
| Select the thing table that will pull in records from other (possibly nested) foreign tables. | |||
| thing table | |||
| Now specify the column in your thing table that specifies the ID of the record in the foreign table you wish to pull in (which itself may pull in records from other--nested--tables). | |||
| foreign key column | e.g. menu_id | ||
| Your selection of a foreign key column determines which RQ table your primary nested records will be pulled from. This is the upper nested table. However, if that table itself is to pull records from other RQ tables, in a nested chain, you need to specify the very last, or lower table to be part of the chain. See the tutorial "Nested Tables: The Restaurant Menu Problem" if you are unsure about the meaning of upper and lower tables. | |||
| lower table | |||
| Pulling data from nested tables can be resource intensive, so it makes sense only to do it where needed. Specify the places where the nested table information should be made available for formatting in the templates. | |||
| extraction locations |
Detail Page (where reviews appear)
Profile Page | ||
In this case, the nested table information will only be automatically extracted when someone is requesting the Supplier Profile page. The restaurant menu is not required when a request for the detail page is made (where the reviews appear for the Supplier in question).
Note also that a lower table needs to be defined. This is the table corresponding to the deepest level of data extraction. For our case, where the menu can only be fully constructed if we retrieve everything down to the menu selections, the MenSelection table acts as the lower table. Had we selected instead, say, the MenuMeal table as the lower table, we would retrieve meal names only (like Lunch Menu, or Dinner Menu) and no sections or selections would be available in the data structure returned for formatting.
In fact, the lower table is used as the starting place for determing what ALL the other tables will be in our set of nested tables. This can be done in a unique way because of the foreign key relations that we have already added to our set of nested tables--of which the lower table is the last.
Next Section: FORMATTING NESTED DATA IN TEMPLATES
Copyright © 2004 Random Mouse Software. All Rights Reserved.