Complete database schema for a Medium-like online publishing platform featuring user profiles, publication management with member roles, article publishing with rich metadata, nested response threads, clap reaction system, bookmarks, user and publication follows, reading lists, personalized notifications, and paid subscription management.
An article names an author and, optionally, a publication. Both keys sit on the row, so a piece written for a publication keeps its individual byline — the writer does not disappear into the brand, and pulling an article out of a publication is clearing one column rather than changing who wrote it.
Claps are not likes, and the schema says so with a count column. article_claps holds one row per reader per article with how many times they clapped, which caps a single person's contribution at one row while letting enthusiasm vary. A likes table could not express that without either many rows per reader or losing the degree entirely.
Paid access is decided by two independent facts: articles.is_member_only and the reader's row in subscriptions. Nothing is written per article when someone subscribes, so the gate is a boolean on the content meeting a status and a period end on the reader.
A plain like is one row per person and answers how many people. A clap has a degree, and the honest options are a row per clap or a count on one row. The count keeps a reader to a single row no matter how enthusiastic, makes de-duplication a unique constraint, and makes the total a sum of counts — at the price of losing the timing of individual claps, which nothing in the product needs.
Requiring a publication would force every personal post into a one-person publication, duplicating the author as a brand. A nullable key lets the same table serve both a standalone post and an edited piece, and makes moving between them an update. The cost is that every listing query has to decide whether it is showing a person's work or a publication's.
Access here is binary: open to everyone, or open to members. A tier system would allow levels, and this schema deliberately does not — which keeps the paywall check to one boolean against one active subscription. If tiers are later needed, this column becomes a level and the subscription needs the same scale, and both changes have to land together.
It could be derived from content length on every render, and it is instead written once. Storing it means a listing of fifty articles does not parse fifty bodies, and it allows the estimate to be adjusted for code blocks and images. The trade is that editing an article must recalculate it, or the number quietly becomes wrong.