Veeam is happy to provide a free NFR license for one year. It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.
COURSE of the MONTH
14 days, 3 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Database design for a dynamic category system
Posted on 2003-02-28
I am currently planning the development of a inhouse Document Managment System.
My orginal idea was this for the overall relationship of data:
Categories>--\
.......................>--
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>--\
.......................>--
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...
My orginal idea was this for the overall relationship of data:
Categories>--\
.......................>--
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>--\
.......................>--
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...
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4
10 Comments
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
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
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
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....
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....
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
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
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.
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.
posted a new question regarding the UI for this, if your interested...
http://www.experts-exchange.com/Web/Graphics/Q_20534800.html
http://www.experts-exchange.com/Web/Graphics/Q_20534800.html
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.
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.
Yep as I said
Another variation is if you want subcategories to be linked to two parents then you would need:
Another variation is if you want subcategories to be linked to two parents then you would need:
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:
Main1
SubMain1
SubMain2
SubMain3
SubSub1
SubSub2
SubMain4
Main2
Main3
Main4
Main5
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.
Menu interface for Categories with each item being a link, and each SubCategory being indented, something like a simple tree view menu:
Main1
SubMain1
SubMain2
SubMain3
SubSub1
SubSub2
SubMain4
Main2
Main3
Main4
Main5
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.
I've got some code to return hierarchies here
www.nigelrivett.com
Retrieve Tree Hierarchy
There is also a lot more discussion at
www.sqlteam.com
www.nigelrivett.com
Retrieve Tree Hierarchy
There is also a lot more discussion at
www.sqlteam.com
Featured Post
Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
- Databases
- AWS
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
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
Course of the Month14 days, 3 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.