Thinking about sorting articles..

[Amy Peters]

Hi,

I run a Perl site where one important aspect is that members can post articles (and other people can comment on them). I'm thinking about how best to structure my table for the various sorting methods for viewing articles.

Articles are arranged in categories and people can view them by date published via each category, or by all articles.

However, I want to offer some additional sorting mechanisms. For example I want to be able to sort and return members, instead of just articles.

So listings of members (perhaps in "last online" order, which is a column in "members" table) who post mostly to a particular category. So it's going to have to go through the "articles" table and work out which category every article is in (by category id), how many articles each member has published and which category they have posted most articles too. That seems like a lot of work and in effect a table scan on both the members and articles tables?!

I'm wondering if it's more economical to somehow work this out as I go along (eg. a table with each member id and a column for each possible category id and how many of each they have (added to by perl script). However, that's essentially repeating information though, which you're not supposed to do in MySQL? Plus I'd still need to compare each column in each member row to see which has the highest value.

Any help or ideas would be great. Thanks