Hey there! Great question—this is a super common design challenge for social network-style apps, and the fix boils down to one key insight: your "users" and "friends" are the exact same entity. You don't need separate tables for them, which is why you're worried about duplicate data. Let's break this down into a clean, scalable database schema that mirrors how Facebook works.

1. The users Table (Single Source of Truth for All People) #

This is your foundational table—every person (whether they're the main user, their friend, or a random user on the platform) gets stored here exactly once. No duplicates, ever.

This table eliminates your duplicate data problem entirely—friends are just other entries in this same table, not separate records.

2. The friendships Table (Tracks User-to-User Connections) #

Since a user can have many friends, and each friend can have many friends, this is a many-to-many join table that handles the relationship between users. It also manages friend request statuses (pending, accepted, rejected) which is crucial for social networks.

How this works: #

• When you send a friend request to Alice, you insert a row where user_id = your ID, friend_user_id = Alice's ID, and status = pending.
• When Alice accepts, you update that row's status to accepted.
• To fetch all of your accepted friends, you'd run a query like this:

  SELECT u.*
  FROM users u
  JOIN friendships f ON u.user_id = f.friend_user_id
  WHERE f.user_id = 'your-user-id' AND f.status = 'accepted';
  

3. The posts Table (User-Generated Content) #

All posts (whether from the main user or their friends) live here, linked back to the users table via user_id. No duplicate post logic needed—every post is tied directly to the user who created it.

Example: Fetching posts from your friends #

To get a feed of posts from your accepted friends, you'd join the posts table with your friendship data:

SELECT p.*, u.username, u.profile_photo_url
FROM posts p
JOIN users u ON p.user_id = u.user_id
JOIN friendships f ON u.user_id = f.friend_user_id
WHERE f.user_id = 'your-user-id' AND f.status = 'accepted'
ORDER BY p.created_at DESC;

4. Optional Extension Tables (For Full Facebook-Like Functionality) #

If you want to add features like likes, comments, or shared posts, you'd build these on top of the core tables:

• post_likes: Tracks which user liked which post (columns: like_id, user_id, post_id, created_at)
• comments: Stores comments on posts (columns: comment_id, user_id, post_id, content, created_at)
• post_shares: Tracks shared posts (columns: share_id, user_id, original_post_id, created_at)

• User ↔ Friendship: One user can have many friendship records (one-to-many), and each friendship connects two users (many-to-many between User and User via Friendship)
• User ↔ Post: One user can create many posts (one-to-many)
• Post ↔ Like/Comment: One post can have many likes/comments (one-to-many from Post to Like/Comment)

By treating "users" and "friends" as the same entity, you never store duplicate user information. All personal details (name, email, profile pic) live in the users table once, and friendships are just lightweight links between existing user records. Posts, likes, and comments all tie back to this single source of truth, keeping your database clean and consistent.

内容的提问来源于stack exchange，提问作者anon anon