C# Hierarchical Categories structure

c++categoriesdata-structuresparent-child

I am developing a small C# windows application and in the need to Hierarchical Categories structure design. I am cuurrently using a single layer Categories from the DB i.e. no child categories. I would like to go about and allow the user to create multiple level categories. I looked into this thread Data structure for Category, but i was thinking if there is an easier way to deal with this sort of problem? because I am not sure if this would be the best solution for the problem.

I would appreciate if someone could provide the DB table structure and some C# code code to go with it. Also I wanted to check if I am able to get all child category ID's (including sub childs) from its parent.

Best Solution

create table Category
(
     id int primary key identity,
     parent_id int,
     name varchar(100),
     foreign key (parent_id) references Category (id)
)

public class Category
{
    private int id; 
    private string name;
    private Category Parent;
    private IList<Category> Children;
}

That is a naive solution to create a tree, and when you're rehydrating your object hierarchy, that solution is going to require a bunch of DB selects. You need to also store some information that will reduce the number of selects, and remember the order of the nodes in the tree.

Joe Celko has written quite a bit on SQL trees that will be far more valuable than anything I can type. I found that link while looking at "More Trees & Hierarchies in SQL" on sqlteam.com

Related Question