A blog and content management system database designed for WordPress-like platforms. Features include multi-author support with role-based access, hierarchical categories, many-to-many tag relationships, nested comments, and content versioning. Perfect for building custom CMS solutions, blogging platforms, or knowledge bases.
This is the shape a content system settles into once editing is a job rather than a hobby: posts holds the current article, post_revisions holds every version that came before, and the two are separate so that reading a blog never touches the history.
Comments are written by people who do not have accounts. author_name and author_email sit on the comment row rather than pointing at users, and is_approved gates publication. That is what makes this a public blog rather than a members' site — an anonymous comment is a first-class row, not a user record created on the fly.
Almost everything else is the usual furniture, with one exception worth noticing: settings is a key-value table with updated_by. Site configuration lives in the database rather than in a file so that an editor can change it, and the column records who did.
post_revisions stores the whole title and content each time rather than a delta. It costs storage and buys the thing that matters: any version can be read or restored in one query, with no replay. For text-sized documents that trade is nearly always right — a diff chain is worth it when the documents are large or the revisions are many, and a blog is neither.
Requiring a user row for every commenter means creating accounts nobody asked for, and it makes deleting a spammer a user deletion. Holding the name and email on the comment keeps the identity where it is used, and is_approved lets moderation happen without any account existing at all.
category_id is a column on the post and tags go through a junction. That is a deliberate asymmetry: a category is where an article lives and drives the URL and the navigation, while tags are labels and can be many. Making both many-to-many loses the guarantee that every post has exactly one home.
status says draft, scheduled or published; published_at says when. Keeping both means scheduling is just a future timestamp, and a post that is unpublished and republished keeps its original date. A single state column would need a second one anyway as soon as scheduling appears.