category, subcategory table. or just category table with parent id?

blackhorse

For category->subcategory->item structure.

1) We can use three tables: category, subcateogry, item.

2) We can use two tables: category, item. cateogry will have parent_id field, the "category" category's parent_id is 0, "subcategory" category's parent_id is its parent category's category_id.

Approach 1) is simple. And you have the flexiblity to design category and subcateogry table and view pages totallly different, with a lot of diffierent fields etc.

Approach 2) is a little tricky, sometimes you would have to use the left join etc. in sql to handle the level 1 category and level 2 category (subcategory) etc. Have to consider is this category a "level 1 category " or "level 2 category" etc. Plus, if your "level 1 category " or "level 2 category" are with very different fields, then one category table covers both "level 1 category " or "level 2 category" will not make too much sense.

But approach 2) has the flexibility to become category->subcategory->subsubcategory ..., the levels of the category in theory is unlimited etc. and also the item (such as the product) could be attached to any level of categories, not only to the bottom level category (subcategory)

I was using approach 2) like zen cart, oscommerce did too. But I found out, almost all my small business online sites to make it easier for the shoppers and merchants we will use strict category->subcategory->item structure. So no need for the flexibility for category->subcategory->subsubcategory ... and also no need for the flexiblity that sometimes products belong to level 1 category, sometimes products belong to leve 2 category (subcategory).

I am thinking approach 1) should be used instead of approach 2 if most of my systems are strict category->subcategory->item structure. Due to approach 2's advantages we never used. But if we use approach 2, we give up simplicity approach 1) has.

I have been switching from approach 1) and approach 2) in the projects of past 10 years, based on the team or the clients requests. But I found out, all projects ended up almost only using strict category->subcategory->item structure. And if that is the case, approach 1) should be used.

What is your experience?

Piranha

I don't have any experience in this, but I have thoughts. Hope that is allowed as well 😃

First let me say that I think that you have covered all the advantages and disadvantages with both ways, at least I have nothing to add to those thoughts.

blackhorse wrote:
But I found out, all projects ended up almost only using strict category->subcategory->item structure. And if that is the case, approach 1) should be used.

And that is the problem. Almost only using... What happens if there are 3 products that should be category->item, then you have to find a workaround for those products. For it to work with category->subcategory->item all products have to be that way without any exception.

But there is a possibility that you haven't talked about. It is possible to use 1 table to store everything that is common among all categories and other tables to handle data that only occurs in one type of category. Something like this:

[b]table category[/b]
id
parentid
name
categorytype
other data

[b]table maincategory[/b]
id references category(id)
producttype // only a bad example
other data that is only used in the main category

[b]table subcategory[/b]
id references category(id)
manufactorer // only a bad example
other data that is only used in the subcategory

It will solve the problems with different data in different categories and give the ability to use as many or as few categories as wanted. But it will make it harder to write queries. As I said in the beginning I only have thoughts about this. If it makes a good solution I don't know for sure. I guess that it is all about how it should be used, all 3 alternatives are good in some projects, and all 3 are bad in others.

blackhorse

Thanks for your thoughts. I have been using the categories (with parent id) approach. Something like zen cart front end and backend. But the merchants need more time to understand it. Such as "subcategory is actually a category" or something like that. It is very straightforward to the computer guys but not for the general public. Even it is not a difficult issue at all. But the merchants will understand category, subcategory, product approach right away due to they used it in the daily life. If they even feel a little difficult, they think your system is not good. So I ended up use the downgraded approach, use 3 tables category, subcategory and product (in the computer guys view points) and they loved it.

If for the site selling 30,000 products, then I will design differently. But we are targetting small to middle size business, our largest is site with 5000-6000 products. 12 categories, 12 subcategories in each category, still only 40-50 products in each subcategory, and we only need 2 pages to display 40-50 products in the list, and it is only 3 layers, not too many layers to get the shoppers lost.

For the site only have a few products in your example. I can custom program it very quickly in a few hours, I can hide off the category level from the merchant backend and shopper frontend, make a dummy category. So to them it is only category->product, but actually it is "hidden dummy category"->subcategory->product.

I am not working on the plug in and turn on system, I am still doing a lot of custom desgin based on the merchants requirements. So small custom changes like this only small part of the whole work. But the merchant will feel that my system is better and easier to use. Even I think the other approach makes more sense in computer science, but I have to use the one make sense to the clients.

I have been spent many hours in the past years like upgrade my backbone database system from category, subcategory, product tables to category (parent id), produt tables, and then "downgrade" it back, several times. Now I am trying to do the new round updates and think I should listen to other people's experience before I go with category, subcategory and product tables structure and will stick with it for a while.

Piranha

Of course it is true that you should make it look like it is category->subcategory->item if that is what the customer want. But that doesn't mean that it have to be stored that way, it only means that it should be showed that way.

blackhorse

Due to the merchants only familiar with category->subcategory->product, I did take the approach even database is categories (parent_id), products approach. I made it looks like category->subcategory->item, frontend and backend.

This is my current approach. Now after many this kind projects, and none of the clients requests break the category->subcategory->item pattern. I begin to question this approach.

If the backend and frontend all looks like category->subcategory->item. If most of the cases, the pattern is category->subcategory->item. So why not just make the backbone database like category, subcategory, item.

That brings my question here. Because I want to switch from looks like category->subcategory->product (but actually it is category and product two tables) to true category->subcategory->product (category, subcategory and product 3 tables, looks and the database structure too).