Is category nesting as a "string chain" a bad idea?

Question

Thinking of building a nested category "system" using "chained strings" for lack of a better term. Here's the plan:

A category slug could be something like "shopping-clothing-womans". This would correlate to a 3 deep category: Shopping > Clothing > Woman's.

An object in the database would have a category field, containing the slug. Let's say there are several objects, with varying slugs in the Shopping > Clothing category, perhaps: "shopping-clothing-mens", "shopping-clothing-kids" and "shopping-clothing-other".

I'd have a collection, or a dictionary, that would translate that slug into something more meaningful for end-users (for example, "shopping-clothing-womans" -> "Woman's Clothing").

If I wanted to select all the objects that were in the Shopping > Clothing category, I'd do something like this:

DB.Objects.Where(a => a.Category.Contains("shopping-clothing"));

And would get back all the womans, mens, kids and clothing subcategories of the Shopping > Clothing category.

The goal is simple querying, yet remain powerful with capabilities of near endless sub categorization without insane DB relations and JOINS. I'm also leaning toward perhaps adapting my application to a NoSQL database in the future, this would help in implementing that.

But, the query above worries me... would it be slow?

Is this plan a bad idea?

Answer 1

This sounds like a good idea. The only direct problem I see is that it would be difficult to rename categories. One way to solve this would be to chain the internal ID's of the categories instead of the codes.

Concerning the Contains, I would probably go for StartsWith. This would produce a LIKE 'shopping-clothing%' and is a lot faster with the correct index.

One more tip: if you append every category with a dash, you won't get problems if one category has the same prefix as another like "shopping-clothing-womans-". Then you can always use StartsWith("shopping-clothing-").

Answer 2

Another approach would be to have a separate category table which contains categories (no shit Sherlock?) that can be nested. Like this:

| ID | Name | ParentId |

In the case that ParentId is 0, it is a main category.

Then in your products table, your category column would refer to a category in the category table. You could then easily select the slug from the categories table.

If you need to search all products within shopping-clothing, your query could look like:

    SELECT * FROM PRODUCT WHERE CategoryId IN 
    (SELECT ID FROM CATEGORY WHERE ParentId = 'shopping_clothing_id')