hierarchical-data

SQL Server Query or Tool to show Hierarchical Data

We have a general organizational table structure, think of it s a Tree or Pyramid Hierarchy. We basically have multiple "trees" we want to show. On for one company, one for another ETC. Does anyone know of a good way to display this data? SQL Query would be nice, doubt it would be possible, but I wouldn't be against using some...

Creating a flattened table/view of a hierarchically-defined set of data

I have a table containing hierarchical data. There are currently ~8 levels in this hierarchy. I really like the way the data is structured, but performance is dismal when I need to know if a record at level 8 is a child of a record at level 1. I have PL/SQL stored functions which do these lookups for me, each having a select * from tb...

optimizing a view for hierarchical data

I currently have a query that contains a self-join to query all the direct and indirect managers of an employee from a table that stores company organization info using the nested sets model. In this SQL notation, numbers preceded by a colon (e.g. :1) are variables: select parent.empid, parent.depth from RelationshipMgr as node join Re...

When to use recursive table

Hi All, I have a need to build a schema structure to support table of contents (so the level of sections / sub-sections could change for each book or document I add)...one of my first thoughts was that I could use a recursive table to handle that. I want to make sure that my structure is normalized, so I was trying to stay away from deo...

How to self JOIN recursively in SQL?

I have a table: Series ======== ID SeriesName ParentSeriesID A series can be a "root" series, (ParentSeriesID is 0 or null) or it can have a Parent. A series can also be several levels down, i.e. its Parent has a Parent, which has a Parent, etc. How can I query the table to get a Series by it's ID and ALL descendant Series' ? So...

Sql Server Analysis Services Parent Child with non-unique key

I'm currently building our Data Warehouse, primarily using Ralph Kimball's methods and guidance. We are using the Microsoft stack for this (so SSIS, SSAS). I am a bit stuck deciding how to handle BOMS (Bill of Materials) which is effectively an unbalanced hierarchy. The BOM handles assemblies which are a collection of parts. Each part...

how to code breadcrumbs (php kohana framework)?

hello! is this good way to code breadcrumbs? create table "categories" using mysql create after neccessary columns "parent_id" column if parent_id column have null value it means that this category is parent and if else this columns parent is a column with id which marked in "parent_id" column recursively query to "parent_id" column st...

Logic for displaying infinite category tree in nested <ul>s from Self Join Table

Hi everybody! Please help me solve my big problem. in my on-line shopping project i created a dynamic Category List (with Infinite Level Depth) Implemented in a Single Table in DB with Self join. the schema is like below: Update I want to use a JQuery plugin to make a Multi Level Menu bar. this plugin uses <ul> and <li> elements so I sh...

Typed Dataset ConstraintException on insert

Basically I have a table that contains hierarchical data. So I have an id column, and a rollup column which points to the id column for each child's parent. This table is loaded from the database using GetData() on the TableAdapter then the table's primary key autoincrementseed is set using: SELECT IDENT_CURRENT('TableName') + 1 ...

Is this possible in SQL?

Let's say I have a table called 'species' with 3 columns: 'Id', 'ancestorId' and 'name'. The 'ancestorId' is the 'Id' of the ancestor specie, so for example if the ancestor of the homo sapiens is the australopithecus, and australopithecus's 'Id' is 5, then homo sapiens's 'ancestorId' is 5. Let's say, also, that there is a specie called '...

How to prevent deep recursive queries with entities consisting of entities of the same type? [Cool example inside]

No worries! It looks more complex than it actually is! Just get down to the drinks! TLDR-version: How to efficiently query and update entities having relationships to other entities? Here's an interesting data modeling scenario with two tables that has been puzzling me: Entities { ID, Name, ScalarValue } ComponentEntities { Aggregate...

Display three level hierarchial data display in ASP.NET Web Forms

The coding is in ASP.NET 3.5. Here is the scenario. I am modifying a page that populates some data. In that page there is a table that displays pages. The pages have sub-pages and these sub-pages again have sub-pages. So thats three levels of hierarchal data presented. Example: Right now its populated using asp:Treeview for easier dis...

MySQL Database - Related Results from same table / Many to Many database design problem

Hello, I am designing a relational database of products where there are products that are copies/bootlegs of each other, and I'd like to be able to show that through the system. So initially during my first draft, I had a field in products called " isacopyof " thinking to just list a comma delimited list of productIDs that are copies o...

Selecting leaf id + root name from a table in oracle

I have a table that is self referencing, with id, parentid (referencing id), name, ordering as columns. What I want to do is to select the first leaf node of each root and have a pairing of the id of the leaf node with the name of the root node. The data can have unbounded levels, and siblings have an order (assigned by the "ordering" ...

50 million node hierarchy or larger

Does anyone out there have any great ideas to achieve a massively scalable hierarchical datastore? It needs rapid add and ability to have many users of site requesting reports on the number of nodes below a certain node in hierarchy. This is the scenario.... I will have a very large number of nodes getting added per hour. Lets say I w...

C# Deep Copying Tree Structures with BinaryFormatter

EDIT 1 Kent has alleviated my fears. However, I've found one exception to the rule. I wrote a method for the Node class that would traverse up through the hierarchy until it got to the root Node and return its hash code. The hash code's the same all the way down the line except for one single object. In a word, RAGE. I'm at the end o...

MySQL Self-Join

Hi All, I have a table (this cannot be changed) like the following: POST_ID | PARENT_ID | POST_NAME 1 | 0 | Services 4 | 1 | Development 5 | 4 | Magento 2 | 0 | Contact The field, 'parent_id' references post_id to form a self-referential foreign key. Is it possible to write a s...

Display hierarchical data

I am playing around with a code example i found here about 'tree menu' and wanted to make this question. function tree($id) { $query = "SELECT `name`,`id` from `table` WHERE `id_parrent` = '$id'"; $result = mysql_query($query); if(mysql_num_rows($result) != 0) { echo "<ul>"; while($row = mysql_fetch_array($result)) ...

WPF Treeview Databinding Hierarchal Data with mixed types

I have a bit of a complex situation with WPF Treeview Binding. I have spent the last 2 days trying Google it, and this is the closed I came up with, but it doesn't solve the issue. Here is the situation: I have an object that looks like this: public class Category { public string Name { get; set; } public List<Category> Categorie...

How do I build a self-referencing Model Object for Hierarchical data in asp.net MVC?

Hello SO, I'm trying to understand how to build a self referencing model for hierachical data. Eventually i will be creating a category tree. I don't anything in tables yet. After I have the structure nailed down then I will create the tables. My existing Object is defined like this: public class Categories { public Int64 catID { ...