Imagine a store with all kinds of different product. Each product has a category with many subcategories. One can have only one subcategory while the other one can have 3 subcategory. Example from Amazon:
Electronics, Computers & Office
Musical Instruments
Guitars
Electronic Guitar
Acoustic Guitar
Monitors
Computers & Tablets
Tablets
Laptops
Desktops
Gaming
Home
Work
So: Monitors have one category level
Electronics, Computers & Office > Monitors
Tablets two category levels
Electronics, Computers & Office > Computers & Tablets > Tablets
And Electronic Guitar Three category levels
Electronics, Computers & Office > Musical Instruments > Guitars > Electronic Guitar
The question is what is the best way to achieve this structure (considering the flexibility, maintenance, accessibility etc)? Somewhere I saw this structure:
Categories table
-------------------------------------------------------
| id | title | caregoty_id |
-------------------------------------------------------
| 1 | Electronics, Computers & Office | 0 |
| 2 | Musical Instruments | 1 |
| 3 | Guitars | 2 |
| 4 | Electronic Guitar | 3 |
| 5 | Acoustic Guitar | 3 |
| 6 | Monitors | 1 |
| 7 | Computers & Tablets | 1 |
| 8 | Tablets | 7 |
| 9 | Laptops | 7 |
-------------------------------------------------------
As you can see there is only one table which has a relationship with itself. I don't know if it is the correct way of doing it. Should I go with this idea or I should do it with more preemptive way by making Categories table like: categores1, categories2, categories3 ... where they have One To Many relationship. More subcategories more tables I will have. But I think it will be difficult to SELECT data from them because we won't know how many subcategories one item have.
Are these ways are correct? Which one should I use or is there a better way to do it? Thank you!