Design an Online Course Marketplace Review System Schema

Design the review feature for an online course marketplace. Once a learner wraps up a course, they're allowed to post a star rating plus a written review, and shoppers browsing the catalog lean on those reviews to pick what to take next.

Touch the whole system, but plan to spend most of your time on the data model: what each table holds, how rows relate and reference one another, what rules the database itself enforces, and how the day-to-day reads and writes actually hit that model. Throughput and infrastructure sizing are yours to assume reasonably — the bulk of the interviewer's questions will stay on the model itself.

Constraints and Clarifications

• Traffic volume, response-time targets, and any per-feature limits aren't handed to you — name the numbers and rules you're choosing to assume.
• Assume the platform already has tables for learners, instructors, courses, course modules, and enrollments; the review feature builds on top of these.

Clarifying Questions

• Is a review for the course as a whole, for individual modules within it, or both?
• Must a learner have an enrollment marked complete to review, or is any enrollment enough? Can a learner review a course more than once if they retake it?
• Once posted, can a learner go back and change or remove a review — and if a dispute or moderation review comes up later, does an older version need to still be recoverable?
• Do reviews support photos or attached code samples? Is there a moderation step before a review becomes visible?
• What's shown to a browsing learner: a rolled-up score and tally for the course, a feed ordered by freshness, a feed ordered by peer-voted helpfulness? Does the instructor get a public reply?
• How quickly after a review is submitted must it be reflected in the course's displayed average rating?

Part 1 — Entities, keys, and constraints

Design the core tables: what a review row contains, how it's tied to the enrollment that earned the right to write it, and what constraints prevent duplicate, orphaned, or otherwise invalid reviews.

Part 2 — Read paths and supporting structures

Walk through the read paths a course page needs (average rating and count, a paginated review feed sorted by recency or by helpfulness) and design the indexes or precomputed structures that serve each one without scanning every review on every request. Cover how an edit or a moderation action keeps those supporting structures correct.