How to properly categorize articles (or anything?)

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

vidya
Forum Newbie
Posts: 6
Joined: Wed Mar 01, 2006 12:46 pm

How to properly categorize articles (or anything?)

Post by vidya »

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
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

I use the following technique for unlimited categories...

table_groups:

Code: Select all

pkid, parentid, name
parentid = 0 means root categorey.

table_articles:

Code: Select all

pkid, catid, content, date, whatever
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 :P

Good job :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Isn't the only thing you have to do is create child and parent categories?

Code: Select all

Table Structure:
Record Number Unsigned Int 10
Parent Category Unsigned Int 10
Child Category Unsigned Int 10
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.

Code: Select all

Parent 0
  Child 1 Parent 0
  Child 2 Parent 0
  Child 3 Parent 0
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.

Code: Select all

Parent 0
  Child 1 Parent 0
    Child 4 Parent 1
  Child 2 Parent 0
  Child 3 Parent 0
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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What's "easy to query" agtlewis?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

The Parent Category
All Sub Categories of a Parent

And

Id's linking to other records in other tables.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

"All Sub Categories" being first generation of children or all of their sub-children?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

First Generation. I have never ran into a situation where I required more than that. What is an example of something that would?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Many menu structures have at least three generations (parent to grandchild) and familial structures (e.g. pear.php.net's package structure) come to mind.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

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.
I'm not sure I follow. :?

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 :)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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. :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Anyone ever try adding grandparent and grandchild fields? That would give you 3 levels.
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post by Gambler »

alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post by alex.barylski »

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. :)
Ahhh...

I have zero experience in anything but MySQL. Even that i'm not very knowledgable in - enough to get me by most times 8)

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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

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. :) Thanks.
Post Reply