News sites/newspapers create stories on a daily basis and make them available to readers. The stories are updated daily, but finding older content is difficult. How can this problem be solved?
An integrated RSS collection/search engine
- An application that collects stories from RSS feeds (feed content and full content from the feed items) and stores them in a database
- A user interface where users can get lists of stories based on search terms
- HTML/CSS/JS files
- Next.js for user interface
- Node.js to run app server
- MySQL for database
- Responsive design: works on desktop and mobile (touch-friendly, no keyboard required)
- Lite-mode theme as opposed to dark-mode theme for user interface
- Use red/green TDD to develop tests
-
At startup, application reads a list of RSS feeds from an OPML file
-
Feeds are read on an hourly basis
-
Only new items are added to the database (if a feed has items that have already been stored, they will not be added to the database)
-
Admin interface supports the following features: -- Username/password for entry to interface -- Ability to create new users -- Method for updating password for users -- Generate report of number of feeds being read, total number of feed items stored in database
-
RSS feeds are stored in a schema using the guidance below:
When designing a MySQL schema for an RSS feed system, you typically need to manage two primary types of data: the sources (feeds) and the actual content (items/articles).
Recommended Database Schema
A standard relational approach uses two main tables linked by a foreign key. This allows you to track multiple feeds and all their respective articles.
- feeds Table
This table stores the metadata for the RSS sources you are tracking.
| Column | Type | Description |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary Key. |
| title | VARCHAR(255) | The name of the feed (e.g., "Planet MySQL"). |
| url | VARCHAR(255) | The direct URL to the RSS XML file. |
| site_url | VARCHAR(255) | The home page of the website. |
| description | TEXT | A brief summary of what the feed covers. |
| last_updated | DATETIME | When the feed itself was last modified or checked. |
- items Table
This table stores individual articles or posts pulled from those feeds.
| Column | Type | Description |
|---|---|---|
| id | INT AUTO_INCREMENT | Primary Key. |
| feed_id | INT | Foreign Key linking to feeds.id |
| guid | VARCHAR(255) | Critical: A unique identifier from the feed to prevent duplicate entries. |
| title | VARCHAR(255) | The headline of the article. |
| link | VARCHAR(255) | The URL to the full article. |
| description | TEXT | The summary or snippet of the article. |
| pub_date | DATETIME | The original publication timestamp. |
| content | LONGTEXT | Full HTML content (optional, if provided by the feed). |
Implementation Tips
• Preventing Duplicates: Use the tag from the RSS XML as your unique check. If a feed doesn't provide one, developers often create a hash of the URL or title to use as a unique identifier. • Automation: Use a cron job to run a parsing script (like a PHP or Python script) every hour to fetch new items and insert them into your items table. • Encoding: Ensure your database and tables use utf8mb4 encoding to properly handle emojis and special characters from various web sources. • Performance: Add an index to the feed_id and pub_date columns in your items table to keep queries fast as your article count grows.
- Ability to query database content with search terms for any table element
- Ability to create HTML or text reports with search results