Design a database schema for a blog with posts, authors, and tags

WHAT THIS TESTS: Whether you can normalize a simple content domain and model many-to-many relationships without data duplication. Even though the prompt sounds junior, senior interviewers listen for whether you instinctively separate entities, use join tables, and apply constraints rather than flattening everything into a single wide table.

A GOOD ANSWER COVERS: First, the users table with user_id as an auto-increment primary key, username, a unique email field, a hashed password column, a role enum restricted to admin, author, or subscriber, plus created_at and updated_at timestamps. Second, a posts table representing each individual blog entry that references users.user_id via a foreign key to establish authorship. Third, a categories table for classification and a post_tags join table that enables a many-to-many relationship between posts and tags rather than embedding tag data inline. Fourth, supporting entities mentioned in the guide such as a comments table for user feedback and a post_views table to capture engagement metrics like views or likes. Fifth, metadata considerations such as publication dates and status fields to distinguish drafts from published posts and to support SEO requirements.

COMMON WRONG ANSWERS: Storing multiple categories or tags as a comma-separated string inside a single posts column, which violates first normal form and makes indexed lookups impossible. Creating a monolithic table that duplicates author details like email and role on every post row instead of referencing a users row. Omitting the role enum or unique constraints on email, which removes integrity controls at the database level. Proposing a schema without foreign keys, leaving relationship enforcement entirely to application code.

LIKELY FOLLOW-UPS: How would you index the posts table to serve a homepage feed sorted by publication date? How would you handle soft deletes for authors or posts without breaking referential integrity? At what scale would you consider denormalizing tag counts or comment counts into the posts table, and what cache invalidation strategy would you use? Would you store draft content in the same posts table or a separate revisions table?

ONE CONCRETE EXAMPLE: If user_id 5 publishes a post and assigns it the tags database and tutorial, the schema stores one row in users for the author, one row in posts with a foreign key to user_id 5, one row per tag in categories, and two rows in post_tags linking that post_id to each respective category_id. When the tag name changes, you update a single categories row; when you need a count of all posts tagged database, you run an indexed join through post_tags without parsing strings.