Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to develop an ecommerce solution but stuck on the categories management issue and don't know how to proceed further.

See, A category will have a parent category, which in-return can also point to another parent and so on, until the root category is reached. I tried to design the following database schema,

Please tell me if that's right ?

enter image description here

OR Is there any tutorial on categories and parent categories that can help me understand this scenario better ?

share|improve this question
up vote 3 down vote accepted

Classic DB answer: "it depends"

A question:

  • Are you allowed to add an item to any category, whether parent of not?
    That is, can a category have either sub-categories or items, but not both?

If yes, your current table is OK

If no, then have a separate self referencing CategoryParent table. Your current category becomes a child of that.

This constrains your items a lowest level category, but allows a hierarchy of categories.

share|improve this answer
So do you think this is a good fit for Database Administrators? I think it's better for Programmers (aka here) since it seems to be more about architecture, and not really about databases. If you do come across some over here that you think we should migrate there, flag 'em, and the mods here can push 'em forthwith. – jcolebrand Oct 1 '11 at 15:57
@jcolebrand: I just check this FAQ and I still reckon DBA.SE. It's quite a narrow case – gbn Oct 1 '11 at 16:00

Your table design for handling hierarchical categories is correct. This is a general purpose design which can handle situations where there is no way to predict exactly how deep the tree will be along any one branch.

What you might want to consider in your design is whether you are going to make any amendments to assist you with the practicalities of navigating hierarchical data in a relational DBMS.

In my answer to this question I discussed the category hierarchy design.

In my answer to this other question I provide some detailed explanation around a practical implementation that makes hierarchical data much easier to work with - at least on the reading side - using concepts known as nested sets and visitation numbers.

share|improve this answer

Assuming that the column CategoryParent is the Foreign Key referring to category_id, and assuming that the CategoryParent column ALLOWS NULL, you are OK.

It is a good to append Foreign Key columns with _fk so that it is clear. Also make sure that the CategoryParent has the exact datatyp as the primary key.

Blockquote Is there any tutorial on categories and parent categories that can help me understand this scenario better ? Blockquote

Your case is a typical self-referencing or hierarchical relationship. This case exists in many cases other than categories, for example, in organization charts.

A similar example can be found here: DataModeling

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.