Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

abdulaziz's avatar

Best way to make multiple categories item database structure

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!

0 likes
2 replies

Please or to participate in this conversation.