The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

[DATABASE] Dealing with product categories

Let's say that I sell products online, and want to allow customers to select a "top level" category (e.g. Binders and Binding Systems), and see a list of related sub categories (e.g. Binders) - the customer selects a subcategory and sees a list of products contained in that subcategory as well as a list of related "groups" to narrow down their search even more (e.g. Ring Binders).

Given this, what would the best solution be?  A self-referencing, all encompassing Categories table?  The original idea was to cleanly separate data by giving each section its own table (e.g. Categories, Subcategory, Group), but I don't see that working out without a lot of intermediate join tables because a product will be displayed at first when the user selects a subcategory (along with all other products related to that subcategory), and also if the user filters further by selecting the group to which that product belongs - a product can only belong to one group, but product needs to be related to both subcategory and group.

Basically looking for something like at this site:

I'm probably giving this more thought than it requires, so I'm almost certain that I'm missing the "simple" solution.
Some Dev
Wednesday, August 27, 2008

You could probably get away with having a big category table without makng a self-referencing hierarchy in the DB.

Cat #2: "Biggest Book/Office Supplies/File Folders, Portable & Storage Box Files"

Cat #1: "Biggest Book/Office Supplies/"

The advantage being that you don't need to keep track of any references.
Tov Are Jacobsen Send private email
Wednesday, August 27, 2008
If categories and sub-categories don't differ in behavior, then one table is fine.  However, if they differ in behavior, then you want to separate the data.

You also want to consider whether a sub-category can be associated to multiple categories, in which case you would have to introduce an additional table to resolve the many-to-many.

Hope this helps.
Chi Hoang
Wednesday, August 27, 2008
I'd go for a single self-referencing table "categories", This will allow for infinite nesting.
You know that requirements may change, if you set it up at X levels, you may need to add an extra level for some categories, you may also need to move a level 3 category to a main category by itself, with this solution these things are easy by only changing data in a single record.

to speed some things up you could also have a table "category_all_children" which would link a category to all it's children.
Totally Agreeing
Wednesday, August 27, 2008
Celko's Trees and hierarchies in sql for smarties may be useful

Was thinking of something like this:
SQL Newbie
Friday, August 29, 2008
I have a single table setup atm which works well apart from inheritance.

I've found that inheritance means you need to either know the specific amount of levels or use recursive selects.

I've now got to the point where I am about to add another set of tables to contain information such as the breadcrumbs, privacy (for inheritance) etc.

This way I can select a single category without having to use recursion to generate breadcrumbs.
Gavin Roberts Send private email
Monday, September 08, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz