1

I am working on an open source application which can allow users to add RSS feeds. All users of the site can read the content of those RSS feeds. It's not just for user's own feeds.

Using PHP and SimplePie library I have created a simple app. I am trying to implement category based listing.

I have a list of RSS/Atom feeds in a database. Each post in the feed can have a category/label associated to it and a single feed can have multiple posts falling under multiple categories.

Question: I want to get only the posts of category "Science".

In worst case, if I have 1000 RSS feeds, should I read posts from each feed and check the category of each post?

Storing all posts of each feeds and its categories is not a good solution.

How best can we have a database design for this?

Database schema is provided below. Nothing is fixed and I am free to change any schema or design.

http://sqlfiddle.com/#!2/2b519/1

NOTE: The fiddle is just for reference and does not need to worry. Its optional as I thought it might help to make others understand.

Improve this question
0

1 Answer 1

Reset to default
2

Looking at your sample DDL in sqlfiddle, it seems like you are trying to store categories about posts, but reckon its a bad idea to store the posts themselves.

Given what it seems you are seeking, I think you will need a posts table with attributes likes (URL, category(ies), title, teaser/details) .. with the feed table's feed-id or feed-url as a foreign key. This posts table can be indexed on a post-id which could also serve as its primary key

If you allow a given post to belong to more than one category, then having a separate categories table makes sense and categories IDs can then be foreign keys in the categories columns of posts.

Why do you reckon its a bad idea to store all posts? I do think that if you are unwilling to store individual posts, then your options are limited into tagging entire feeds with categories, and that does not sound like what you are seeking to do.

Again, if the volume will grow rapidly and you are worrying about indexing and performance, perhaps you should look into graph DBs or some other NOSQL DBs as faster alternatives ..

Improve this answer
6
  • 1
    The problem I face is, categories are not known in advance. to know them, we need to parse all the feed posts.. Remember feeds can get updated very often too..
    – Purus
    Commented Apr 28, 2014 at 16:31
  • OK, I understand. But if you don't make an effort to parse and categorize the feeds before storing them, then how comes you want to be able to retrieve posts based on some categories? .. I would parse the feed, and extract individual posts, and make a go at categorizing them, or use a default 'unknown' category if unable to do so. Another option is to create an interface in your application, where an editor could go and attempt to categorize posts marked as 'unknown' (ie, introduce some human element in the chain) .. It all depends on the bigger picture of your application.
    – Chux Uzoeto
    Commented Apr 28, 2014 at 18:16
  • How about categorizing the feed sources themselves, and then providing for sub-categories of posts within them? .. These sub-categories could be part-automatically extracted (if you can find an efficient logic for that) or manually edited/added.
    – Chux Uzoeto
    Commented Apr 28, 2014 at 18:24
  • User will be just adding the feed url and the application should take care of categorizing it based on the individual feeds. I don't see any other better solution..
    – Purus
    Commented Apr 28, 2014 at 19:16
  • I just took a quick look at the RSS spec, and it does have a category field .. Granted this is an optional field for both RSS and ATOM feeds, and come as a pair of entities (category, category-domain). The category field has a clearly defined format, and can contain more than one element. Have you considered this? Or perhaps this field is hardly present in the feeds that you are working on?
    – Chux Uzoeto
    Commented Apr 28, 2014 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.