C# Hierarchical Categories structure

Question

Hello Guys

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.

Answer 1

If your hierachy is rigid and will not change, you can hard code it.

The thread is about hierarchies that are fluid and may change constantly, and in that scenario the discussed solutions are appropriate.

Answer 2

Harvinder,

Have you considered using TreeView control for your purpose? I guess this would be a perfect idea. Please have a look at MSDN site regarding TreeView control. Click here.

Answer 3

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

class Category
{
  int id; 
  string name;
  Category Parent;
  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

Answer 4

Hi Guys

Thanks for the replies and consideration for reading my question.

I currently already have a structure for a single category settings, pretty easy I know. I was thinking of taking a slightly simpler route (I think it is simpler route but might be wrong).

I am currently thinking of adding an extra column in the categories table called children_ids. So that all the parents will keep a record of there children rather than the other way around. I children_ids column can be of Text type and ids could be stored in a string format i.e. 1-4-5-7-8 etc. and once I get this column from the DB I can split the string with '-' and get all the ids for its children.

I think this way it will be slightly easier for me to keep an eye on all the population ;), just ask the parent about their children. I think it will also ease the dependency serches as well because I will only need to get a list of all the children (all levels below recursively) quicker I guess. This way I can also sort all the entires before loading them up from the DB, another headache gone.

I am sure there has to be better solutions out there, but don't know if it would be easier or not.

My other requirement was to create a dropdown combobox with this category child-parent style, similar to the folder list structure for the users to choose through them. Maybe something like CodeProject example or CodeGuru example, I might use either one of the approaches for make my life a little easier.

Th problem is I would like to add more details within each record of the dropdown menu i.e. cat_id etc but dont want them to be visible to the user, this is to get details on the user selections. I guess I will have to compensate it by haveing a seperate ArrayList maybe with all the details of the categories and then just go to its index location once the user selected a record from the dropdown list. Am I thinking straight?

Thanks for reading and for the replies!

Answer 5

Harvinder,

The database structure you're thinking about will work perfectly, but it has few disadvantages:

• simple sql query finding all children of specified node is not possible (you would have to perform string operations)
• sorting by number of children is impossible from within sql query
• to get a collection of node's children you will have to connect to the database several times (to get every child separately by its ID)
• etc.

The most common practice is to store single ID (parent) in one column. In this case multiple children can point at the same parent (relation one-to-many), which solves your problem efficiently.

The second part of your question can be answered easily: unless you really have to, don't use Windows Forms (both authors used it) - you will find yourself extremely tired binding your datastructure to your view. It is a far better idea to use WPF for your purpose and flexibly modify Combobox and Treeview Datatemplates to face your requirements. If you're not familiar with WPF please start by having a look at this brilliant article concerning WPF and MVVM design pattern - it does even contain a Treeview examples, which will be helpful in your case.

Please tell me if my answer solved your problem. I'd be happy to answer any of your questions concerning WPF.