Database Design of Categories

I am creating a website that has categories such as:

Sports -> Soccer -> Soccer Shoes

I can not think of an effectively way to implement this into a database. Please help.

Here is a website that has a bunch of sample database designs. If you don't find exactly what you want, the process of looking thru the other designs should help you.

http://www.databaseanswers.org/data_models/

A good data modeling book is by David Hay, Data Modeling Patterns: Conventions of Thought. He gives sample high-level data designs and talks thru different modeling alternatives so you fully understand the implications of subtle modeling differences.

|||

If you post what you've tried and why you didn't like it, I'll be happy to give you ideas on how to improve it.

|||

Thanks, that is a really cool site! I haven't looked at all the database designs because there are a lot of them but one that I found is #39 Books and Libraries. They have a "categories" table but all it stores is the CategoryID and the CategoryName. With just that information how to do you know where the category is in the hierarchy of categories? For example, you have Literature -> Books -> Fiction, say Fiction has CategoryID 20, how do you know where Fiction is in the hierarchy just by knowing the CategoryID?

I came across aneBay developer site that explains how they store their categories. What they do is they store the CategoryID but also the ID of it's parent. This way you know where it exists in the hierarchy. This is what the table would look like:

Category
CategoryID
ParentID
CategoryName
IsLeafCategory

What does everyone think of this solution? Does anyone have any alternative solutions?

|||

A better column name for ParentID would be ParentCategoryID. It's always better to be very clear.

This is a more powerful model than Literature->Books->Fiction because it can handle a "infinite" number of hierarchical levels.

It is also a more complicated one to use than a model in which you force the same number of levels in the hierarchy by using a different table for each level in the hierarchy.

IsLeafCategory is a redundant piece of information in that it can be deduced when required. I would not make use of it unless you have a specific need for it (because then you have to write code to make sure it is always correct).