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