approach 1) products will be organized in fixed three and only three levels

category->subcategory->products

so even for a category only has one subcategory, we still handle it like category->subcategory->products

for a large category, we will still have to end up with.
category->subcategory->products

this approach will not allow dynamically add subcategory levels.

approach 2) products will be organized into different level of sub-categories, the levels of the subcategories are dynamically added/edited.

such as it could be

category->subcategory->products
category->subcategory->sub..category->products
category->products

in theory, the subcategory levels could be dynamically added from 0 to n levels.

Here are the advantages/disadvantages of approach 1) and approach 2)

approach 1) advantages/disadvantages

a) It is simple. Simple to program, update, management. Simple to custom design the category and subcategory pages. Even simple to use for the merchants or shoppers.

b) Because it is simple, the category->subcateogry and subcategory->product could be in "many to many" table relationships. Which add some flexibility for the merchants who wants one subcategory belongs to more than one categories. or one product belongs to more than one subcategories.

approach 2) advantages/disadvantages

a) It is more difficult than fixed 3 levels approach. So it is also more difficult to custom design the category page, and the subcategory page differently. Because in this approach, the category and subcategory are the same actually.

b) It will also make the shopping process a little difficult to custom design. If it is fixed 3 level, only 3 situations we need to consider, you are in category, or subcategory or product page. But for this dynamic approach, the subcateogory level could be in different for each category. So to avoid the confusion, we may just have to make call category and sub..category pages look the same.

c) It is difficult for making the category level dynamically added but at the same time, make the category->subcategory, subcategory->subsubcategory, sub..category-products "many to many" table relations. So we will have to use "1 to many" relations here. So we win the flexibility in adding the levels of category but we lose the flexibility of make the products belong to different subcateoies etc.

d) But dynmaic add levels of category could make this system good for category->product, or category->subcategory->product, or cateogry->n levels subcategory->products. Also it is more like a real life (or unix/windows folder structure).

Most of our clients will be small to middle size business,

fixed category->subcategory->product catalog system applies to them pretty well.

But once a while, they may end up with a category only have one subcategory. or sometimes, they may also wish to have category->subcategory->subsubcategory->products. But the chances are very rare that they need it.

Now here comes my questions

1) To design a more general purpose / easy to use, easy to custom modify, simple system, should I stay with fixed category->subcategory->product system?

or

2) Should I go for the dynamic added levels system.

I prefer approach 1. Dynamical add subcategory level is cool. But it did bring some difficulties and make some custom features difficult to apply. Too many levels of subcategories will make the end user (shopper) confused too.

Any opinions?

Thanks!

    Rigidity is a Bad Thing®, because it doesn't allow for scalability. If you are building this as a generic product for multiple customers, or you are building this for a customer that may have an expanding inventory, then I recommend you use expanding subcategories.

    It's really not that difficult to do. Treat every category the same. Create a column in your category table called cat_parent, and either include the id of the category or sub-category above it, or use "0" to designate a top-level category.

    This structure will allow you to display all sub-categories for any given category, with ease. It will also allow you to create a breadcrumb_builder function or a breadcrumb class, which crawls up the category "tree" until if finds a category where cat_parent = "0".

    I'm sure you can find many examples of this on hotscripts.com. If you want a working example, check out the Yahoo Directory.

    The thing to remember: If you are doing this for a client, then ask THEM which they prefer. Be sure you are thorough about what it will take for each approach, the pros and cons, etc., so they can make an informed decision.

    Best of luck, whichever way you go!

      OK, this way, the category->sub category will be "1 to many" relationships, it will not allow "many to many" relationship. But given the choice that allow the scalability or allow the sub category belongs to more than one category, you prefer scalability, right? (not so many chance that a sub-category should belong to more than one category.)

      But at the same time, we can still allow the category->product to be "many to many" relationship. (so a product could belong to more than one category/subcategory, which may have some demands.)

      Thanks!

        If you have a product that belongs to many different sub-categories, and any one category can contain many products, then you have a many-many relationship. In that case, you need an intermediate table that links the products to the categories. This is a very common practice.

        However, you should have a one-many relationship between categories and sub-categories. Remember, though, that a category and sub-category are the same thing -- the only difference being what id exists in the cat_parent column. So, the relationship there is in the same table. There is a very similar database structure discussed in our PHP book (not trying to plug, just trying to help 🙂 ) in chapter 10.

        The short answer: No, do not put a sub-category into more than one category.

          I agree that the best way is having a Category and SubCategory where Products can be part of one to many SubCategories. The entity relationship would like like this...

          Category <- one to many -> Sub Category
          Producty <- one to many -> Sub Catetory

            Write a Reply...