A complete social network database schema designed for platforms like Facebook or Twitter. Includes user accounts with detailed profiles, post creation and sharing, threaded comments, bidirectional friendship management with status tracking, and post/comment likes. Suitable for building social media applications, community platforms, or any project requiring social interactions.
Friendship is mutual here, and that single fact shapes three tables. friend_requests holds the directional ask with a status; friendships holds the accepted result as a pair of user ids; blocks holds the refusal. A follow model would need only one of these — mutual consent is what forces the request and the connection to be separate things.
likes points at both post_id and comment_id, with one of them null on every row. It is the compromise that keeps a single like table serving two kinds of target: the alternative is two nearly identical tables, or a polymorphic pair with no foreign keys at all. Here the keys stay real at the cost of a column that is empty half the time.
Counts are cached on the thing being counted. posts carries like_count and comment_count, comments carries like_count, and none of them is derived at read time. A feed renders hundreds of posts at once, so each one has to arrive with its numbers already on the row rather than as two aggregates per item.
One table with a status column would hold both, and every friend lookup would then have to filter on that status — the most common read in the product paying for the rarest state. Separating them keeps friendships to accepted rows only, and lets a declined request be deleted or retained without touching the graph. The cost is that accepting is a delete and an insert in one transaction.
A polymorphic target_type plus target_id would be tidier and would give up foreign keys entirely. Two nullable columns keep referential integrity for both kinds of like, at the price of a check constraint — exactly one must be set — and a column that is null on every row of the other kind. With two target types that trade is worth it; with five it would not be.
Each post names its own audience rather than inheriting one from the author. That lets a public post and a friends-only post sit in the same timeline, and makes the feed query a filter rather than a per-author lookup. What it does not support is custom lists — a named group of friends needs its own tables, and that is the first thing to add when visibility stops being a small enum.
A block has to work between people who were never friends, which a friendship status cannot express. Keeping blocker_id and blocked_id separate also keeps it directional — being blocked is not symmetric, and the blocked person should not be able to read it from their own friend list.