I’m trying to create an e-commerce catalogue with categories and subcategories. Each product can be in more than 1 cat/subcat. I also would like to have the flexibility to add more levels of subcategories.
Now my question is: if I have a category with only 1 level of subcats, and another one with several levels (i.e: countries > US // cities > US > MA > Boston), what structure would you suggest for the database?
I was planning to have separate table for products and each category level, with another table linking a product to one or more categories (linking product_ID to category_ID), but I’m not quite sure how to work with different levels of categories, if not all of the main categories have the same amount of subcat levels.
Could you help me with some thoughts/best practices on the matter?
i was sure someone would tell you to use the nested set model
in case someone does (and now that i’ve posted, i’m sure they will), i should warn you that the application logic for maintaining a nested set model table is pretty horrendous, although if you’re a natural born programmer, you might like that
are you absolutely sure you want products to be able to belong to more than one category/subcategory? if so, you need a total of three tables (categories/subcategories, products, and product-category relationships), but if a product should belong to only one category/subcategory, then you need only two tables
Hi r937! Thanks for your reply. I’ve been looking at those models you sent me and like the adjacency model much better. But then this guy says that it’s sooo much slower than the other one.
I’m nowhere near a born programmer, that’s for sure. For a site with tons of products but a relatively stable and not very deep category structure (I’m hoping 4 levels at the very most), should I go for the easy one?
And yes, I will want products to be in several categories… but that part I can do.
Use the adjacency model you won’t need to drink as much. Better yet use Oracle which has hierarchical query support! However, if that is not an options use the adjacency model.
