Efficient sub-category coding

[Name Trader]

I don't really know how to ask this question. But when I talk about efficiency, I'm asking about
1) The Coding
2) The MySQL Speed
3) The Website speed.

My current software has only 1 level of categories, and it's kinda getting inefficient. So I want to add sub-categories. How is the most efficient way to do this?

My primary goal here is, in the front-end, to display the top-level categories, and then drill down to the sub-categories, or sub-sub-categories to the products, instead of displaying all the categories together, as is what is happening now.

1) Should I just add the top level categories and define them as top-level, and define and point the subcategories to them? How to do that?
2) Should I go for a numbering system. 1-99 are top level categories, 1001-1999 are subcategories of 1, etc. How to do that?
3) Some other method altogether?

For flexibility, products should be able to go to more than 1 category or subcategory. Or even a subcategory could go to more than 1 category (although in practice I'm not doing this).

I'm not going to be doing the coding for this. I'm just looking for the best method to specify to a programmer. So I would like some comment about how easy or hard this is going to be for the PHP programmer.

 

[Name Trader]

Or. To put it another way. How would you specify this change to a programmer, and how much would you be willing to pay?

 

[Towab Muhammad Yusuf]

Installed and add the shortcode [amazon template=preview script] to the ... to be the product's title, '%PRICE%' the formatted product's price, etc. ... then copying the generated output into the 'Template Content' box. .... 4.3 out of 5 stars.

 

[Name Trader]

I'm talking about PHP/MySQL, not Wordpress?

 

[Chicken]

You could do it a few different ways.

One being just have a field ParentCategory and define that for all subcategories. That would allow you to have unlimited nested categories, to get the top level ones you would just be querying the ones without a parent category.

Ex.

Your root categories are

1. Fruits
2. Meats
3. Vegetables

So they would all have no Parent Category

Then say for fruits you add

4. Apple
5. Pear
6. Raspberry

So for each of those the Parent Category is 1.

It's simple for multiple parent categories, for example for 7. Tomato the Parent Category would be an array (ex. 1,2) to include it in both Fruits and Vegetables.

As for how much to pay, that all depends on how involved it would be to modify your existing code.