Managing Hierarchical Data in My. SQL — Mike Hillyer's Personal Webspace. Introduction. 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. 183 responses to “Saving Form Data to Google Spreadsheets Using PHP and the Google Docs API”. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent- child relationship that is not naturally represented in a relational database table. For 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). 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. For our purposes we will use the following product category hierarchy from an fictional electronics store: These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in My. SQL, starting with the traditional adjacency list model. The Adjacency List Model. ![]() 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(. INT AUTO_INCREMENT PRIMARY KEY. VARCHAR(2. 0) NOT NULL. INT DEFAULT NULL. INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2). LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7). CD PLAYERS',6),(1. WAY RADIOS',6). SELECT * FROM category ORDER BY category_id. ELECTRONICS | NULL |. TELEVISIONS | 1 |. I have two tables, and want to update fields in T1 for all rows in a LEFT JOIN. For an easy example, update all rows of the following result-set: SELECT T1.*. ![]() TUBE | 2 |. LCD | 2 |. PLASMA | 2 |. PORTABLE ELECTRONICS | 1 |. MP3 PLAYERS | 6 |. FLASH | 7 |. CD PLAYERS | 6 |. WAY RADIOS | 6 |. In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see that. FLASH is a child ofmp. 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. Retrieving a Full Tree. The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self- join: SELECT t. AS lev. 1, t. 2. name as lev. If somebody is seeking to update data from one database to another no matter which table they are targeting, there must be some criteria to do it. · This article is one of the several new blogs from the AzureCAT team discussing common customer implementations and proven architecture patterns using SQL. FROM category AS t. LEFT JOIN category AS t. ON t. 2. parent = t. LEFT JOIN category AS t. ON t. 3. parent = t. LEFT JOIN category AS t. ON t. 4. parent = t. WHERE t. 1. name = 'ELECTRONICS'. ELECTRONICS | TELEVISIONS | TUBE | NULL |. ELECTRONICS | TELEVISIONS | LCD | NULL |. ELECTRONICS | TELEVISIONS | PLASMA | NULL |. ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |. ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |. ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |. Finding all the Leaf Nodes. We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query: SELECT t. FROM. category AS t. LEFT JOIN category as t. ON t. 1. category_id = t. WHERE t. 2. category_id IS NULL. TUBE |. | LCD |. PLASMA |. | FLASH |. CD PLAYERS |. | 2 WAY RADIOS |. Retrieving a Single Path. The self- join also allows us to see the full path through our hierarchies: SELECT t. AS lev. 1, t. 2. name as lev. FROM category AS t. LEFT JOIN category AS t. ON t. 2. parent = t. LEFT JOIN category AS t. ON t. 3. parent = t. LEFT JOIN category AS t. ON t. 4. parent = t. WHERE t. 1. name = 'ELECTRONICS' AND t. FLASH'. +- -- -- -- -- -- -- +- -- -- -- -- -- -- -- -- -- -- -+- -- -- -- -- -- -- +- -- -- -- +. ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |. The main limitation of such an approach is that you need one self- join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity. Limitations of the Adjacency List Model. Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub- tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client- side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub- trees by promoting one child element and re- ordering the remaining children to point to the new parent. The Nested Set Model. What I would like to focus on in this article is a different approach, commonly referred to as the Nested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers.Try picturing our electronics categories this way: Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes: CREATE TABLE nested_category (.INT AUTO_INCREMENT PRIMARY KEY. How To Upgrade Sky Digibox Software Download here. VARCHAR(2. 0) NOT NULL. INT NOT NULL. rgt INT NOT NULL. INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,2. TELEVISIONS',2,9),(3,'TUBE',3,4). LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',1. MP3 PLAYERS',1. 1,1. FLASH',1. 2,1. 3). CD PLAYERS',1. 5,1. WAY RADIOS',1. 7,1. SELECT * FROM nested_category ORDER BY category_id. ELECTRONICS | 1 | 2. TELEVISIONS | 2 | 9 |. TUBE | 3 | 4 |. LCD | 5 | 6 |. PLASMA | 7 | 8 |. PORTABLE ELECTRONICS | 1. MP3 PLAYERS | 1. FLASH | 1. CD PLAYERS | 1. WAY RADIOS | 1. We use lft and rgt because left and right are reserved words in My. SQL, see http: //dev. So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right: This design can be applied to a typical tree as well: When working with a tree, we work from left to right, one layer at a time, descending to each node’s children before assigning a right- hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm. Retrieving a Full Tree. We can retrieve the full tree through the use of a self- join that links parents with nodes on the basis that a node’s lft value will always appear between its parent’s lft and rgt values: SELECT node. FROM nested_category AS node. AS parent. WHERE node. BETWEEN parent. lft AND parent. AND parent. name = 'ELECTRONICS'. ORDER BY node. lft. ELECTRONICS |. TELEVISIONS |. TUBE |. | LCD |. PLASMA |. | PORTABLE ELECTRONICS |. MP3 PLAYERS |. FLASH |. | CD PLAYERS |. WAY RADIOS |. Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same parent as the lft values. Finding all the Leaf Nodes. Finding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find the leaf nodes, we look for nodes where rgt = lft + 1: SELECT name. FROM nested_category. WHERE rgt = lft + 1. TUBE |. | LCD |. PLASMA |. | FLASH |. CD PLAYERS |. | 2 WAY RADIOS |. Retrieving a Single Path. With the nested set model, we can retrieve a single path without having multiple self- joins: SELECT parent. FROM nested_category AS node. AS parent. WHERE node. BETWEEN parent. lft AND parent. AND node. name = 'FLASH'. ORDER BY parent. lft. ELECTRONICS |. PORTABLE ELECTRONICS |. MP3 PLAYERS |. FLASH |. +- -- -- -- -- -- -- -- -- -- -- -+Finding the Depth of the Nodes.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |