I've started coding a directory site as a side-project and am busy with the database design, and I've come up to a part I'm not so sure how to do. I'm trying to come up with an efficient way to do a scalable category system, and currently I have a single table that lists the categories, with each entry consisting of a 'parent_category' (the category above the current one) and 'child_category' (the categories below the current one) field. What will happen is that if you view a category page it'll search the categories table and return a resultset consisting of all the rows where parent_category or child_category matches the current category id. Does anyone know of a better way to do this?
You can use the parent_category column to determine all the childs, so your child_category column is redundant and is not needed.
I don't know if it is right or not, but I have only had three columns in every category table I've used: category_id,category,parent_category Of course, you can add extras such as category description, but as far as usability goes this works for me.
haha, yeah, i realised that about 5 seconds into coding the thing This is probably the simplest way to do it so I'll stick to it. Only thing that annoys me is doing the navigation bread crumb for the category (eg parent cat -> sub cat -> sub cat etc). I pretty much have to start at the bottom cat and search the table each level up to try and see if that was the last one.