Yes. This isn’t really the right place to ask a design question. So, I hope I don't get blasted for answering here.
First, I don't think you quite know what you're trying to solve yet. But to help with what you have here, I think you're going to need a stored procedure that returns your result set to you. If you are using MySQL I would re-write my category table as: create table category ( category_id int unsigned not null auto_increment, parent_id int unsigned, category_name varchar(25) not null, property varchar(25), primary key category_pk (category_id), foreign key category_fk1 (parent_id) references category (category_id) )engine=InnoDB; The stored procedure would cycle thru and fill in the blanks for you. But if your tree gets very large I think this will become impractical. Another way to handle it would be to fill in the blanks on an insert. your insertions may be slow, but the lookups should be faster. -Ian
