Adding sub-categories to database application.

[JoePage]

I am working on a inventory application and I have a question, I have created a table for each of the main categories that I will be using, now if I wanted to add sub categories would I create another table for the sub directory and set the type as a child for its corresponding category or would it be easier to keep the main categories and just add another column in each table for "sub category" so it uses the same table but I can just filter it out easier?

If it is a better option for creating parent and child tables, how would I go about listing them in a hierarchical treeview to show parent and child sub-categories in them so I can display appropriate tables.

[techgnome]

I would have one table Category... it would look something like this;l
ID
Name
Description
ParentID (this would FKEy back to the ID field of this table)
LEvel --- maybe, maybe not... kind of on the fence on this one....

Then to query it out, to get all the top level ones, you'd select where ParentID is null (and/or level is 0)
Then to get the children, you would grab them based on the ParentID where it matches the ID on the 0-level records.... does any of this make sense?

Depending on the DBMS you're using, and how you plan to use the data, there are different ways of extracting it out in a way that you can use.

-tg

[Shaggy Hiker]

Creating a parent child relationship is more normalized, but using a different column may provide performance benefits. Therefore, it is something of a trade-off as to which you do. One issue with sub categories is that they have a tendency to grow over time. Once you allow in the concept of sub categories, there tends to be more and more sub categories identified. That may make the parent child relationship a better alternative, especially if you want the parent to fit into multiple different sub categories. If you will never have anything fitting into more than one sub category, the sub category begins to sound an awful lot like an attribute of the item, in which case just adding another column makes more sense.

Another way to say that is: It depends.

Not a very definitive answer, but I hope it conveys the idea that this is one where there is no answer that is right for all situations. To some extent, you will have to take your best guess and accept that you may later decide that you were wrong.

[JoePage]

I've been experimenting with both options and I went with the added column for sub category and just filter it out like that, it seems alot easier and it keeps the database simple.

[JoePage]

Techgnome, im not really completely sure what you mean are you saying you would have 1 table for each category so lets say..

Category:Chains
-ID
-Name
-Amount
- Parent ID

Category:Rope
-ID
-Name
-Amount
- Parent ID

That is the structure I am using right now except I added a column for sub category aswell. Now I am wondering if I should of went the other way because I am having trouble filtering them.

I have a listbox which is bound to the subcategory column so it lists all the items in sub category, now I have multiples in the list so it shows multiples in the box of the same sub cat and also I cant get it to show all the rows in the table with that sub-cat I can only get it to select it's corresponding row(the one it came from)

[techgnome]

No, that's not what I said. I said I would have one table... period. One. Single. Solitary. Uno. Ein. No more. No less.

The one table would be called "Category"
IT would have the following fields:
ID - number; autonumber/identity
Name - string - this is the name of the category
Description - string/text - optional description of the category
Amount -- not sure what this is, but presumably it would be a numerical field of somekind
ParentID - number - it would be the ID from the parent category.

so an example of the data would look something like this

1 - "Chains" - "" - 0 - 0
2 - "Chairs" - "Things to sit on" - 0 - 0
3 - "Wicker" - "Wicker chairs" - 100 - 2
4 - "Wooden" - "Wooden chairs" - 10 - 2

see? It's all in one table. There's no need to create multiple tables like you've described... makes it harder to build efficient queries.

-tg

[JoePage]

Got it, sorry I was just a little confused. How would I go about filtering the information by category, lets say using a listbox or combobox. I have the column "category" items loaded in the listbox, although there is multiples of the category names..because it just displays it as a list of all the items in the category column, so when its bound to my gridview when I select the listbox item it selects the corresponding row...I want the listbox to show no multiples and at the same time filter only the rows in that category...I've tried some different approaches but i cant get it working.

[techgnome]

you query the data:

SELECT Name FROM Category

That will give you all categories, main and sub and sub-sub.... if you only want to show the main categories:

SELECT Name FROM Category where ParentID is null

Actually, you'll probably want the Name and the ID

SELECT Name, ID FROM Category WHERE ParentID is null

Then bind that datatable to your list, setting the DisplayMember to Name and the ValueMember to ID...

-tg