How to properly categorize articles (or anything?)
Posted: Wed Mar 01, 2006 1:14 pm
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