Design a Social Network Database (Relational)

Design a relational database schema for a social networking platform with users, posts, friendships, and interactions.

Functional Requirements

• User profiles with personal information and settings
• Friend/follow relationships (bidirectional or unidirectional)
• Posts, comments, and nested replies
• Likes and reactions on posts and comments
• Direct messaging between users
• Notifications for social activities

Non-Functional Requirements

• Support hundreds of millions of users
• Handle viral content with millions of interactions
• Enable efficient feed generation queries
• Maintain referential integrity while allowing soft deletes

Questions to Consider

• How will you model the friend/follower graph efficiently?
• What's the best way to handle nested comments?
• How do you optimize for feed generation queries?