Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Database design for a dynamic category system

Posted on 2003-02-28
Medium Priority
Last Modified: 2008-02-26
I am currently planning the development of a inhouse Document Managment System.
My orginal idea was this for the overall relationship of data:

But then I thought "I don't want to be stuck with only 2 levels of categories, I would like the system to be much more flexible.  So I came up with this overall relationship concept:


Its not obvious in this due to my lack of ASCII art skills, but SubCategories was removed, and a new field was added to the Categories table called ParentID so that it looks like this:
ID     Name     Description     ParentID
1      Cat1     Cat Desc1       0
2      Cat2     Cat Desc2       0
3      Cat3     Cat Desc3       0
4      Cat4     Cat Desc4       0
5      Cat5     Cat Desc5       1
6      Cat6     Cat Desc6       1
7      Cat7     Cat Desc7       3
8      Cat8     Cat Desc8       9
9      Cat9     Cat Desc9       7

Categories records now have a relationship setup with each other.  Any category with a ParentID of 0 is a top level category, and all others refer to another category as its parent.  i.e. Cat1 is a top level, and Cat5, and Cat6 are subcategories of Cat1.
This at first seemed like my solution.  But as I began thinking about the various logic involved, and program flow I relized that this would require tons of code to maintain the integrity of the data.
1. No parent level item (Categories, or Documents) can be deleted unless all of its children have first been removed.  This would be difficult to program given the dynamic aspect of the Categories.
2. It would take lots of code to keep Categories from having circular references.  This would cause confusion for the user and infinite loops for code that examines the child level items.

Can anyone think of a better database design for this, that still offers a dynamic category system with out using multiple tables for each category level?
Point will be given to the best idea, and additional point will be give to other who contribute ideas based on how useful they were.

Question by:Da_Weasel
  • 5
  • 4

Expert Comment

ID: 8045837
Its the way i did it, too. you have to climb from categorie to categorie when you are showing them but you can handle this
LVL 18

Expert Comment

ID: 8046634
Put in a root cayegory with ID 0 ParentID null and make ParentID referencing ID. This will solve the problem about leaving orphan categories.

You can enforce a rule that the child ID must be greater than the parent ID to enforce no circular references.
This will only cause a problem if you move categories between paraents but ...
The code for both these rules isn't as complicated as you might think if you want to do it like that.

This is the usual way to model this sort of requirement.

Another variation is if you want subcategories to be linked to two parents then you would need:

ID Name Description

ParentID, ChildID


Author Comment

ID: 8046880
that root category idea is why i have to 0's in the parentID field for the first four.  Anything with a ParentID of zero is a root category.  (BTW, ID is a autonumber field)
I had thought about the idea of enforcing the rule: ParentID must be less than ID, but should a existing category need to be moved later, it would require renumbering the ID field.

Another idea I thought might help was to add a new field called Level which would store a number that represents the child/parent level.  i.e. root categories are 0 and any direct children are 1, thus when a category is clicked, you can select categories with parentID = [selected cat ID] WHERE level is [selected cat+1].  This would clear up most of the issues involved with dynamic categories and make the logic of the application much easier.  This also has a problem when it comes to moving categories.  Should it switch levels, then it would need to be updated to have a level = parent-level+1 and each of its children on down the line would need to be adjusted accordingly.

I will leave this open till tomorrow in hopes of drawning in more discussion, and then award points.

nigelrivett: what would be stored in the ChildID field?  I don't follow...

Thanks for the idea....

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 18

Accepted Solution

nigelrivett earned 400 total points
ID: 8046939
It needs the 0 ID for the foreign key unless you change the o's to nulls.

Your table would become two tables

ID     Name     Description
1      Cat1     Cat Desc1
2      Cat2     Cat Desc2
3      Cat3     Cat Desc3
4      Cat4     Cat Desc4
5      Cat5     Cat Desc5
6      Cat6     Cat Desc6
7      Cat7     Cat Desc7
8      Cat8     Cat Desc8
9      Cat9     Cat Desc9

ParentID  ChildID
1          5
1          6
3          7
9          8
7          9

Author Comment

ID: 8048223
ok, i see what your are saying, setups a seperate table to maintain the Child/Parent relationship in.  Cool, I could then get all of the category info that I need with a simple JOIN.
So this would be a One-to-Many relationship.  One Parent has many children.
Looks like a solution to me ;), will award point...I think the rest of the data is ok, but if you have any ideas about the rest of it, i would love to hear it.

Author Comment

ID: 8048352
posted a new question regarding the UI for this, if your interested...

Author Comment

ID: 8074647
I just relized that the ChildID field will never repeat a value, which mean that it will present the same data that is already available in the ID field of the Categories table.  So making this new table to hold parent/child relationships has only UN-Normalized the data.

Just a note for anyone that stumbles upon this, the above answer did not actually solve the problem.  I failed to throughly review the answer offered, before accepting it.
LVL 18

Expert Comment

ID: 8078053
Yep as I said

Another variation is if you want subcategories to be linked to two parents then you would need:

Author Comment

ID: 8078492
Right...thats might prove interesting....I will work out the code to get the menu based interface (example below) for the current setup working first and then try this, and post my finding here.

Menu interface for Categories with each item being a link, and each SubCategory being indented, something like a simple tree view menu:


The difficult part was, figuring out that the currently open branch of Categories had to be built in reverse.  This required making a function that finds the TopLevelParent for any given category and also one to get a count of the number of levels a category is from its TopLevelParent.
LVL 18

Expert Comment

ID: 8079895
I've got some code to return hierarchies here

Retrieve Tree Hierarchy

There is also a lot more discussion at


Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question