Query for updating a node in a hierarchial tree structure foamy the squirrel dating advice script

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).

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( category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, parent INT DEFAULT NULL ); INSERT INTO category VALUES(1,'ELECTRONICS', NULL),(2,'TELEVISIONS',1),(3,'TUBE',2), (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7), (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6); SELECT * FROM category ORDER BY category_id; ------------- ---------------------- -------- | category_id | name | parent | ------------- ---------------------- -------- | 1 | ELECTRONICS | NULL | | 2 | TELEVISIONS | 1 | | 3 | TUBE | 2 | | 4 | LCD | 2 | | 5 | PLASMA | 2 | | 6 | PORTABLE ELECTRONICS | 1 | | 7 | MP3 PLAYERS | 6 | | 8 | FLASH | 7 | | 9 | CD PLAYERS | 6 | | 10 | 2 WAY RADIOS | 6 | ------------- ---------------------- -------- 10 rows in set (0.00 sec).

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.

But I don't think I've ever seen an article on my way of managing hierarchies in SQL, and that's using what I call a closure table.

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.

There's the obvious way of simply giving records a "pointer" (foreign key) referring to their parent.

The upside is, it's easy and it's normalized: the data means exactly what it says and there's no duplication.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.This leaves two numbers for each node, which are stored as two attributes.The other day I was looking around at "tree" model libraries for Django, and I noticed that there are an awful lot of algorithms people use to manage hierarchies in SQL out there, but the one that is simplest (at least in my view) seems to not be very widely known at all.The term was apparently introduced by Joe Celko; others describe the same technique using different terms.

Tags: , ,