A complete news feed database schema designed for content-driven applications and news aggregation platforms. Includes user profiles, article management with categories, threaded comment system, and bookmark/save functionality for personalized reading lists. Suitable for apps like Flipboard, Google News, or custom news reader solutions.
The schema is small on purpose: five tables covering publishing, reading and saving. articles carries a status and a published_at separate from created_at, which is what lets a draft exist for days before going live — and lets a scheduled piece be written now and appear later without a second table.
One users table serves both writers and readers, split by a role column. A news site has very few authors and very many readers, and the same person is often both, so separating them would duplicate accounts to express a distinction that one column already makes.
comments nests through parent_id and bookmarks is a plain pair of keys. Neither carries any cached count, which is the honest consequence of a schema this size: view_count on articles is the only aggregate kept, because it is the one number a listing sorts on.
A single timestamp would make the front page order by when a draft was started, which is wrong for anything written in advance. Keeping both means the newsroom can order by publication while retaining the real authoring date, and a null published_at is an unambiguous not-yet-live state that no status string can contradict.
News URLs are read by people and ranked by search engines, so the address has to carry the headline rather than a number. Storing the slug means the canonical URL is a column rather than something regenerated from a title that might later be edited — which is what quietly breaks every inbound link when a headline is fixed after publication.
A views table would support unique readers, referrers and time-of-day analysis, and would grow faster than every other table combined. A counter on the row answers the one question a news site actually sorts on — what is being read — at the cost of being an opaque number that cannot be broken down or recomputed.
role on users is enough to tell them apart, and it keeps commenting, bookmarking and writing on the same identity. The limitation shows up with guest contributors: an author who never logs in still needs a row, and an external byline has nowhere to go except an account that will never be used.