Database design for hierarchical categories

[MrRuz]

Hi, I'm new here, but I'd like to become an ongoing part of the forums.



My goal is to build an all encompassing personal accounting software in Access. Test it, understand it, and, eventually, move it to a web hosted database.
But for now, I have a steep learning curve and I'm hoping you guys can help me out.

I'm having a problem trying to figure out how to build/query/design the categories (income, expense) in a hierarchy. I have built two tables, the first is as follows:

Table 1

ID_Category	CategoryName	CategoryDescription	Parent_ID
1	Parent0	parent category	null
2	Parent1	sub-category	1
3	Parent2	sub-sub-category	2

Table 2

Level0	Level1	Level2
Parent0	null	null
Parent0	Parent1	null
Parent0	Parent1	Parent2

I have successfully managed to query the attributes from Table 1 to Table 2 with four queries, But I'm not sure if I'm doing this correctly, or if there is a better way to do it. My end goal is to be able to go to the category through forms and move it to another level at any time.

Also, I'd like to be able to show all the categories in an easy to use view, ie.:

• Parent0
  
  • Parent1
    
    • Parent2

Doing two levels wasn't that difficult, but the third level (level2) was kind of hard.

Thoughts anyone?


This will be an ongoing project and I think I'll be around awhile.

Thanks in advance.

[ranman256]

It seems you only need the 1 table (self relating)
each item has a [parent] field. (much like a geneology program)
parent of a parent of a parent. etc.

[MrRuz]

Oh okay. So put the attributes all in Table 1 and manually do the parent structure? I guess that would be a lot easier.