In a database with a category tree or hierarchy what is a best practice method for getting additional data from higher up the tree or hierarchy?
example
category 456 is called bluewidgets and has a parent category numbered 123 called widgets. In the database each row has a unique catnumber as well as a catname and parentcatnumber. There is also a 4th table with the tree level, ie: 1,2,3 or 4.
what I want it to do
I'm trying to build a breadcrumb navigation system but there are 10's of thousands of entries in the database so I need an efficient method of extracting the parentcatnumber and catname of that parentcatnumber. If the catnumber I specify is 3 levels deep I will need the parentcatnumber and name of the level 2 and level 1 categories it belongs to.
best practice
Would it be better to leave the database at 4 tables and to extract the data via php or would it be better to create additional tables and store the name and numbers of any "higher up" categories into each row?
My thinking is that the data is in the database so I shouldn't need to duplicate it inside the database by adding entries to other entries... but I'm not sure how I'd extract the data efficiently via php either.
Has anyone had experience they are willing to share in building a breadcrumb like feature where the information is in multiple rows?
