This month, Rebecca Riordan looks at two simple techniques for expanding the standard Lookup tables. First, she shows you how Lookup tables can handle hierarchies, but then she moves on to show how to manage state transitions–the movement of an entity from one state (married, for example) to another (separated or divorced). As you’ll see, even complex transition rules can be modeled using a very simple variation of the standard Lookup table.
Lookup tables are common in modern database applications, and their implementation is well-understood. So common and well-understood, in fact, that Microsoft added intrinsic support for Lookup fields to Microsoft Access (many database professionals consider Microsoft’s implementation ill-advised, but that’s a different issue).
However, as I’ll show you in this article, you can also use Lookup tables to ensure consistency in the changes allowed within your application.
In your applications, you almost certainly have some entities that change state: A purchase order may go from Entered to Open to BackOrdered to Shipped to Paid. In your application, you may have written a great deal of code to ensure that an entity only goes through a limited number of allowed changes (a purchase order may not be allowed to go straight from the Entered state to the Paid state but is allowed to go from Shipped to Paid). Rather than write a great deal of code, you can manage these transitions through Lookup tables. First, a digression to look at hierarchies.
A simple extension of the standard Lookup allows you to manage hierarchies. Figure 1, for example, shows a Category/Product hierarchy derived from the Northwind sample database. The second combo box is based on a simple filtered query:
SELECT Products.ProductName FROM Products WHERE CategoryID=Forms!HierarchicalLookups!Categories;
Figure 1
In this example, the form is named HierarchicalLookups and the combo box listing the Categories is named (surprise!) Categories. In code, you can use the Column property to reference any column in a multi-column combo box. In a query, you can only reference the bound column, so in order for the reference to the combo box to work, the BoundColumn property must be set to the column that returns the key field. In this example, that’s column 2, CategoryID.
In order to update the second level of the hierarchy as the user makes choices in the Categories combo box, a Change event handler calls the Requery method of the combo box containing the product list (named Products in the example):
Private Sub Categories_Change() Me.Products.Requery End Sub
There’s nothing particularly unique or tricky here, yet this simple technique is extremely powerful. Not only can this technique be extended to handle hierarchies of arbitrary depth, but a simple variation can also be used to manage state transitions.
State transitions
Although many entities can change state, you don’t always need to manage the transition. For instance, sometimes the transitions are unrestricted. The status of an Order, for example, can move from Open to Shipped or Cancelled more or less arbitrarily–at least the application is unlikely to impose any constraints on the change. Sometimes the transitions are strictly linear. The age group of a Customer can only move from 18-21 to 22-25 (unfortunately). In these cases, the application is likely to handle the transition without user intervention.
But sometimes the possible state transitions are restricted and also unpredictable. A classic example is an individual’s marital status. A simplified diagram of the possible states and transitions is shown in Figure 2. As you can see, a given individual can change status from Never Married to Married, and from Married to Separated, Divorced, or Widowed, but can’t return to Never Married.
Figure 2
The figure is a classic state transition diagram. These diagram types aren’t included in analysis methodologies, but they’re extremely useful for modeling complex transitions. As you can see, the diagram consists of “t’s” for each possible state. Possible transitions are represented by a simple arrow from the original state to the resulting state. The diagrams are easy to understand and to draw freehand. Unfortunately, they’re not supported by Microsoft Visio (I created these diagrams in Adobe Illustrator).
Implementing transitions in tables
There are two ways to model transitions in a relational database. The simplest is to use a single table, as shown in Figure 3. The table contains a record for every possible change in status. In your code, as you change the status of an entity, you can simply look up the old status and the new status to see if there’s a record that includes both–if so, the transition is allowed. Alternatively, you can present the list of possible transitions to the users and let them select the transition that they want to make. The corresponding form from the sample application is shown in Figure 4 (you’d probably want to limit the list of acceptable transitions to those that start from the entity’s current state).
Figure 3
Figure 4
The form is essentially the same as the one displaying hierarchical lookups that I showed you back in Figure 1. The first combo box is based on a SELECT DISTINCT statement:
SELECT DISTINCT SimpleTransitions.CurrentState FROM SimpleTransitions;
This query is unsorted. In reality, you’d probably need to implement a custom sort order field to control the order in which the items are displayed to the user (I’ll show you an example of this technique shortly). The second control on the form is a listbox rather than the combo box that I used for the hierarchical example, but the filter expression (Forms!SimpleTransitions!CurrentStatus) and the call to Requery in the Change event are the same.
This simple technique will work perfectly well for many situations, but if the possible transitions are subject to change–unlikely with marital status, but perfectly possible for, say, order status–these tables can be difficult to maintain, simply because it’s difficult to track what transitions are allowed by looking at the transition table. In these more complex situations, it’s more effective to use either two or three tables. Figure 5 shows the schema for a two-table implementation. The StateID field in the MaritalStates table is a standard autonumber, linked to both the CurrentStatusID and PossibleStatusID fields in the JoinedTransitions table.
Figure 5
A three-table implementation is used when the two states are logically distinct entities that are most effectively stored in separate tables. The structure, in this case, is a classic many-to-many resolved by a junction table that models the possible transitions.
Figure 6 shows the contents of the MaritalStates table. The table contains an Autonumber key field and a sort field. Neither is strictly necessary. The StateID autonumber conserves space in the table that records the possible transitions (JoinedTransitions in the sample), while the SortOrder field compensates for the lack of inherent order in the fields.
Figure 6
The form for this version of the application is identical to the single-table version. The CurrentStatus combo box is based on a simple SELECT query:
SELECT MaritalStates.State, MaritalStates.StateID FROM MaritalStates ORDER BY MaritalStates.SortOrder;
Again, in order for the filtering to work, you must specify the StateID column that’s used as the foreign key in the JoinedTransitions table as the BoundColumn property of the combo box. The listbox that displays the possible states is the most complex of our examples, but still quite simple:
SELECT MaritalStates.State FROM MaritalStates INNER JOIN JoinedTransitions ON MaritalStates.StateID = JoinedTransitions.PossibleStatusID WHERE CurrentStatusID=Forms!JoinedTransitions!CurrentStatus;
The MaritalStates table is joined to the JoinedTransitions table on the PossibleStatusID field. If you have created relationships between the two tables (and you should), Access will probably create two joins when you add the JoinedTransitions table to the query; you’ll need to delete the one to CurrentStatusID.
In our sample, we’re only displaying the description (contained in the State field), so it’s the only one returned by the query. In a real-world application, you’d probably need to return the StateID as well. As with our other examples, the records returned are filtered by the value of the CurrentStatus combo box control on the form. Finally, the Change event handler of the combo box calls the Requery method of the PossibleStatus listbox to keep the list of possible new states synchronized.
These extensions to Lookup tables aren’t particularly difficult to implement; you’ve probably done something similar many times. But they do represent a slightly different way of thinking about certain kinds of relationships in your database schema, and representing them to the user.
Or You Could Try
What’s in a Table?
Managing Lookup Tables
Everything About AutoNumbers
