troubleshooting Question

Database design for a dynamic category system

Avatar of Da_Weasel
Da_WeaselFlag for United States of America asked on
Databases
10 Comments1 Solution383 ViewsLast Modified:
I am currently planning the development of a inhouse Document Managment System.
My orginal idea was this for the overall relationship of data:
Categories>--\
.......................>--SubCategories-->--Documents>--Files
Keywords>----------------------/

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:

Categories>--\
.......................>--Documents>--Files
Keywords>----/

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.
Examples:
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.

Thanks...
ASKER CERTIFIED SOLUTION
nigelrivett

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros