While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL. The adjacency list model has the advantage of being quite simple, it is easy to see that FLASH is a child of mp3 players, which is a child of portable electronics, which is a child of electronics. The topmost element, in this case electronics, has a NULL value for its parent. In the adjacency list model, each item in the table contains a pointer to its parent. SELECT * FROM category ORDER BY category_id Typically the example categories shown above will be stored in a table like the following (I’m including full CREATE and INSERT statements so you can follow along): CREATE TABLE category(Ĭategory_id INT AUTO_INCREMENT PRIMARY KEY, In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model. These categories form a hierarchy in much the same way as the other examples cited above. For our purposes we will use the following product category hierarchy from an fictional electronics store:

Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.įor our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for.

Hierarchical database example