ESTABLISHING A HIERARCHY
There are various ways to store hierarchies. The simplest form is where each item has a single parent, and each parent can have zero or more children. Consider a database that stores academic questions. The questions can be grouped by category. In a Category table, each record describes a specific category, ranging from the general, like Math, English, and Science, to the more specific, such as Addition, Trigonometry, and Chemistry.
The primary key of the Category table is an AutoNumber field called “CatID.” It also has a foreign key to itself called “CatID_” that stores the primary key value of the parent category. (Appending an underscore to the end of the primary key field name is how I indicate the field has a relationship to a parent record.)
When rendering a hierarchical data structure, you need to decide how many levels you’ll provision for. For example, one category in our table is Math (CatID=1). Another category is Arithmetic (CatID=26). Its CatID_ is set to 1 to indicate that its parent category is Math. At the same time, Arithmetic is also a parent. For instance, the Addition category has CatID=2 and CatID_=26.
In this hierarchy, therefore, there are two upper levels. In human terms, this is equivalent to a parent and a grandparent: Math → Arithmetic → Addition. Many hierarchical structures also need a great-grandparent.
When the Addition category was first created, it was put directly under Math. Later, the Arithmetic category was created and assigned to be the parent category for Addition. One of the advantages of this design is that making such an adjustment is a simple change.
Instead of making a self-join, which is what we’re doing with having the CatID_ field join to another record in the same table, a common approach would be to create more tables. For instance, one might be tempted to make a table for categories, a separate one for subcategories, and another for subsubcategories, and so on. But where does it end? Defining a data structure to be flexible for use now and in the future helps you create a successful application. So if you decide you want more levels—like when Arithmetic was added—you won’t have to change the underlying data structure to add them.
SHOWING THE HIERARCHY
Assuming a particular category can have up to three levels above it in the hierarchy, a query can be constructed to show all the connected categories by relationship, starting with parents (Category1) and then any higher relationships when applicable, such as grandparents (Category2) and great-grandparents (Category3).
Here’s a query showing categories and subcategories for Math:
As the category at the top of the hierarchy (i.e., it has no parent), Math doesn’t have anything listed in Category1, Category2, or Category3. The others all have at least a parent. For instance, Improper Fractions is a subcategory of Fractions, which is under Arithmetic, which is under Math.
Here’s the query in Design view:
You’ll see that the same table is used four times. Access adds an underscore and number to the end of the name of each copy to make it unique, e.g., Category_1, Category_2, and Category_3. For each set of tables, a relationship is created between the parent CatID_ field to the primary key CatID field in the next table up.
The first four columns in the design grid are the Category fields from each copy of the table. Each field is given an “alias” of Category#, where # corresponds to its position up from the Category listed in the first column. Likewise, the CatID fields are named in the same fashion.
Given that the Category table has the CatID AutoNumber field that could relate to CatID_ on another record, here’s the SQL statement to list categories and parent relationships up to three levels:
SELECT Category.Category
, Category_1.Category AS Category1
, Category_2.Category AS Category2
, Category_3.Category AS Category3
, Category.CatID
, Category.CatID_ AS CatID1
, Category_1.CatID_ AS CatID2
, Category_2.CatID_ AS CatID3
FROM ((Category
LEFT JOIN Category AS Category_1
ON Category.CatID_ = Category_1.CatID)
LEFT JOIN Category AS Category_2
ON Category_1.CatID_ = Category_2.CatID)
LEFT JOIN Category AS Category_3
ON Category_2.CatID_ = Category_3.CatID
ORDER BY Category.Category;
Hierarchies like this are a great way to group and organize data within Access. By specifying a parent record in the same table, it’s easy to roll up calculations, as is often done in accounting. For more complex hierarchical information, such as a bill of materials for manufacturing assemblies, when one part might be used in several different assemblies, you can create a cross-reference, or junction, table to relate parent and child records.
Download this month’s file: SF2011_Hierarchy.
SF SAYS
A simple hierarchical relationship can be defined by creating a foreign key field in a table that relates to its own primary key.
November 2020
