Assume you have a flat table that stores an ordered tree hierarchy: Id Name ParentId Order 1 'Node 1' 0 10 2 'Node 1.1' 1 10 3 'Node 2' 0 20 4 'Node 1.1.1' 2 10 5 'Node 2.1' 3 10 6 'Node 1.2' 1 20 What minimalistic appro...
I am translating SQL Server SQL Statements into their ANSI generic equivalent at present, and am stuck with a recursive statement using a WITH statement. For the sake of concentrating on the issue, I'll simplify the issue as follows If I have two tables ReportingUnit col1: Key col2: ParentReportingUnitKey Facility col1: Key col2...
I'm creating a threaded message board and I'm trying to keep it simple. There's a message table, and then a replies table that has an 'reply_id' field that can be null to indicate a top level response, or a value that indicates a threaded response. I'm a bit confused on how to do a SELECT call on this type of table though? Reply -id...
I know there are two approaches: adjacency list and nested tree. It's said that adjacency list can become slow to use on traversal because of numerous queries. But I don't know any realistic figures for this. The site I'm making will have in the region of 200 pages. Is traversal to generate (for example) a sitemap going to take longer th...
I have an AdvancedDataGrid (ADG) with a HierarchicalData dataProvider: <mx:AdvancedDataGrid xmlns:mx="http://www.adobe.com/2006/mxml" dataProvider="{__model.myHierarchicalData}" displayItemsExpanded="true" sortExpertMode="true" dropEnabled="true" sortableColumns="false" draggableColumns="false" resizableColumns="true"...
In a previous application that I had written, I had a Class that extended AdvancedDataGrid (ADG). It contained the following code: package { public class CustomADG extends AdvancedDataGrid { .... // This function serves as the result handler for a webservice call that retrieves XML data. private function...
I'm not aware how deep my tree will be. So I think the NSM is fit for me, reading some docs. In sql, this model suppose I'm using an integer value as primary key. I thought to create a twin table only to store the ints (PK,left,righ) connected by a relation one-to-one with the real table. Things are complicating and it is a waste of spac...
I have a typical table of hierarchical data in id, parentId form. CREATE TABLE Hierarchy (Id int, ParentId int NULL, Name varchar(128)); INSERT INTO Hierarchy VALUES (1, NULL, '1'); INSERT INTO Hierarchy VALUES (2, NULL, '2'); INSERT INTO Hierarchy VALUES (3, NULL, '3'); INSERT INTO Hierarchy VALUES (4, 1, '1.1'); INSERT INTO Hierarchy ...
I have a MySQL database table with this structure: table id INT NOT NULL PRIMARY KEY data .. next_id INT NULL I need to fetch the data in order of the linked list. For example, given this data: id | next_id ----+--------- 1 | 2 2 | 4 3 | 9 4 | 3 9 | NULL I need to fetch the rows fo...
What are 'best-practices' for saving Composite patterns in a Relational Database? We have been using Modified Preorder Tree Traversal. This is very quick to build the whole tree, but very slow to insert or delete new nodes (all left and right values need to be adjusted). Also querying the children of a node is not easy and very slow. A...
Essentially, I want the user to be able to define a hierarchical model, but then I need to allow the user to store data within their defined model. Does this make sense? So the users will be able to create new "unit types" to be organised in a hierarchical way and decide how units of these types are allowed to be organised. A simple exam...
I am writing a LINQ provider to a hierarchal data source. I find it easiest to design my API by writing examples showing how I want to use it, and then coding to support those use cases. One thing I am having trouble with is an easy/reusable/elegant way to express "deep query" or recursion in a LINQ statement. In other words, what is th...
I was hoping someone could explain how one goes about updating a nested set. I've looked at http://dev.mysql.com/tech-resources/articles/hierarchical-data.html, but it really only deals with adding and deleting nodes. I need to be able to move nodes with and without child nodes. Any help would be appreciated. ...
I'm trying to fetch a genealogy tree of animals from my Oracle database. Here's the table: Animal ------------------------ Animal_ID Parent_Male_ID Parent_Female_ID .... .... ------------------------ If I specify an animal, I can get all of its descendants (on the male side) using something like this: SELECT * FROM animal START WITH...
Duplicate: SQL - how to store and navigate hierarchies If I have a database where the client requires categories, sub-categories, sub-sub-categories and so on, what's the best way to do that? If they only needed three, and always knew they'd need three I could just create three tables cat, subcat, subsubcat, or the like. But wh...
I'm working with a large hierarchical data set in sql server - modelled using the standard "EntityID, ParentID" kind of approach. There are about 25,000 nodes in the whole tree. I often need to access subtrees of the tree, and then access related data that hangs off the nodes of the subtree. I built a data access layer a few years ago b...
Hi, I have an external datasource that implements IHierarchicalEnumerable. I'm trying to use that datasource for my TreeView, but I can't find a way to specify the images and individual navigation urls the control should render (there is some business logic there). The examples I have seen all assume the Url and the Name and Image fiel...
I have three tables A: A.pID primary key, A.Name nvarchar(250) B: B.pID primary key, B.Name nvarchar(250) C: C.pID primary key, C.Name nvarchar(250) There is a m to n relation between A and B (table lA_B with primary key lA_B.pID and .pInstanceA Foreign key to table A and .pInstanceB Foreign key to table B) There is a m to n rela...
I'd need a MySQL query that moves a node and all it's children within a nested set. I found this site, but that function just seems so illogical - there's no universeid or treeid in a nested set model, and the code itself it just longer than what feels required. The only extra column I've got in the table is "parent". I couldn't just re...
I have a hierarchical query to track a reporting structure. This almost works, except that it's not reporting the very top level node, probably because the top-level people "report" to themselves. The query is: select level, empid, parentid from usertable connect by nocycle prior parentid= empid start with empid = 50 This pro...