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.
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.