How to properly categorize articles (or anything?)
Moderator: General Moderators
How to properly categorize articles (or anything?)
I have an interesting challenge for a CMS I'm developing for a client.
We want to categorize articles (with subcategories, too.)
It would be simple enough if an article had only 1 category-subcategory,
but the client wants an article to have up to 5 categories it's in.
First, I have to write the routine that allows definitions (configurations) of the categories
and subcategories. If only categories were wanted, it would be a simple list of input fields where the
last field was always blank (when filled in, that's how a new category is added.)
But he wants each category to have subcategories. So the way I've designed this in the past,
is to display a list of the categories with a link on each category that, when pressed, went to a page
where subcategories could be defined for that category. (Plus an Add Category button for defining a
new category)
That's awkward. And time-consuming to define new categories. Is there a better way?
The database would have a Category table. And a SubCategory table. And each entry in the subcategory table
would have an ID which points back at the Category table (so it "knows" which category its in.)
Actually, configuring categories isn't the hard part. Or should I say, when setting up the category-subcategory tables,
it is essential to get the database relationships correct, as the program which display articles by category could be either easy-to-write
or hard-to-write, depending upon how the database is setup.
For example, how do I setup the articles table with a field representing categories? If I put an ID in a field that points at the subcategory
entry (which gives me the category, too) do I have 5 such fields in the article table? Obviously not. It seems I have to have another table
in the middle which contains an ID for the article and an ID for the subcategory. And there would be 5 entries in this table, if an article had 5 categories.
Which gets complicated, when we want to setup a page that lists the categories-subcategories (and how to do that without it being a huge page) listing every possible category-subcategory?
And if there was to be a number next to the category representing the number of articles in this category, it would be one heck of an SQL statement to come up with, when showing this page. And probably slow as can be.
Anyways, I hope this post is legible. And you see what I face. And hopefully, have some suggestion of a better way to do it, than I've described.
Thanks much.
...Vidya
We want to categorize articles (with subcategories, too.)
It would be simple enough if an article had only 1 category-subcategory,
but the client wants an article to have up to 5 categories it's in.
First, I have to write the routine that allows definitions (configurations) of the categories
and subcategories. If only categories were wanted, it would be a simple list of input fields where the
last field was always blank (when filled in, that's how a new category is added.)
But he wants each category to have subcategories. So the way I've designed this in the past,
is to display a list of the categories with a link on each category that, when pressed, went to a page
where subcategories could be defined for that category. (Plus an Add Category button for defining a
new category)
That's awkward. And time-consuming to define new categories. Is there a better way?
The database would have a Category table. And a SubCategory table. And each entry in the subcategory table
would have an ID which points back at the Category table (so it "knows" which category its in.)
Actually, configuring categories isn't the hard part. Or should I say, when setting up the category-subcategory tables,
it is essential to get the database relationships correct, as the program which display articles by category could be either easy-to-write
or hard-to-write, depending upon how the database is setup.
For example, how do I setup the articles table with a field representing categories? If I put an ID in a field that points at the subcategory
entry (which gives me the category, too) do I have 5 such fields in the article table? Obviously not. It seems I have to have another table
in the middle which contains an ID for the article and an ID for the subcategory. And there would be 5 entries in this table, if an article had 5 categories.
Which gets complicated, when we want to setup a page that lists the categories-subcategories (and how to do that without it being a huge page) listing every possible category-subcategory?
And if there was to be a number next to the category representing the number of articles in this category, it would be one heck of an SQL statement to come up with, when showing this page. And probably slow as can be.
Anyways, I hope this post is legible. And you see what I face. And hopefully, have some suggestion of a better way to do it, than I've described.
Thanks much.
...Vidya
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
I use the following technique for unlimited categories...
table_groups:
parentid = 0 means root categorey.
table_articles:
catid is the pkid in the categorey tables
This is a possible scenario for category table, to demonstrate:
1, 0, Main categorey one
2, 0, Main categorey two
3, 1, Main categorey one - sub categorey
4, 2, Main vategorey two - sub categorey
There is much work to be done, but it gives a general outline as to how you can easily implement unlimited categories. For instance, you might need to watch out for adding articles to anything but leaf node categories...
Root
Level 1
Level 2
Level 2 is a leaf node, and Level 1 is it's parent, you may not want to be able to add articles to Level 1 categories...
Interesting topic...I've often wondered how others might do this as well...
Hope I understood your question properly...and don't sound like a total dumbsmurf
Good job
table_groups:
Code: Select all
pkid, parentid, nametable_articles:
Code: Select all
pkid, catid, content, date, whateverThis is a possible scenario for category table, to demonstrate:
1, 0, Main categorey one
2, 0, Main categorey two
3, 1, Main categorey one - sub categorey
4, 2, Main vategorey two - sub categorey
There is much work to be done, but it gives a general outline as to how you can easily implement unlimited categories. For instance, you might need to watch out for adding articles to anything but leaf node categories...
Root
Level 1
Level 2
Level 2 is a leaf node, and Level 1 is it's parent, you may not want to be able to add articles to Level 1 categories...
Interesting topic...I've often wondered how others might do this as well...
Hope I understood your question properly...and don't sound like a total dumbsmurf
Good job
Isn't the only thing you have to do is create child and parent categories?
For example the Master Category Parent would be 0. All main categories would be marked with as a Child of 0 and would be assigned a unique Parent ID.
Then to make a subcategory for Child 1 you create an an entry in the database that had it's parent Category marked as 1.
Using this method you can create unlimited categories, subcategories. Of course it would then be easy to add fields in the table for article id's etc and it's easy to query.
Code: Select all
Table Structure:
Record Number Unsigned Int 10
Parent Category Unsigned Int 10
Child Category Unsigned Int 10Code: Select all
Parent 0
Child 1 Parent 0
Child 2 Parent 0
Child 3 Parent 0Code: Select all
Parent 0
Child 1 Parent 0
Child 4 Parent 1
Child 2 Parent 0
Child 3 Parent 0-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
I'm not sure I follow.feyd wrote:That's pretty much how I do tiered structures Hockey. Haven't really found a good cross-database way of performing the selection on it, other than grabbing the entire thing.
How do you mean cross database way of selecting, other than grabbing entire thing?
Apologies, but it's Monday...well actually it's Thursday, but it feels like Monday
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
A single statement that works in all (or most) database platforms (MySQL vs PostgreSQL vs SQL Server). Since I have to deal with ones that allow subqueries and those that don't, it can be difficult at times to find a solid select that works across them. Although I haven't spent a lot of time trying to figure it out... too much other stuff to worry about first. A simple select all and build via PHP works well and is portable enough for my tastes. 
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Ahhh...feyd wrote:A single statement that works in all (or most) database platforms (MySQL vs PostgreSQL vs SQL Server). Since I have to deal with ones that allow subqueries and those that don't, it can be difficult at times to find a solid select that works across them. Although I haven't spent a lot of time trying to figure it out... too much other stuff to worry about first. A simple select all and build via PHP works well and is portable enough for my tastes.
I have zero experience in anything but MySQL. Even that i'm not very knowledgable in - enough to get me by most times
You can recursively SELECT all categories and sub-categories using MSSQL using a single SQL statement? Is that the purpose behind sub queries or sub-selects?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Hmm, the nested set model looks very interesting and quite simple. I've never thought about it in that way. I may have to use that.Gambler wrote:http://dev.mysql.com/tech-resources/art ... -data.html