Design a Database Schema

Design a database schema for the following application:

Application description: [describe what the app does]
Database type: [PostgreSQL / MySQL / SQLite / MongoDB]
Key entities / objects in the system: [list them — e.g. Users, Orders, Products, Reviews]
Key relationships: [describe how entities relate to each other]
Scale expectations: [e.g. small hobby project / thousands of users / millions of records]

Provide:
1. Entity-relationship overview (described in plain English)
2. Full schema with:
   - Table/collection names
   - All columns with data types, constraints, and defaults
   - Primary keys and foreign keys
   - Indexes (explain which columns to index and why)
3. Any junction tables needed for many-to-many relationships
4. Soft delete strategy (if applicable)
5. One example query for the most common operation this schema will power