Database.Middle.How would you design a schema for a social network?

Designing a schema for a social network involves balancing core functionality, scalability, and query efficiency. Here’s a structured breakdown:

🧱 1. Core Entities and Relationships

👤 Users

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100),
    password_hash TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

👥 Friendships / Follows

Depending on whether the network is mutual (like Facebook) or asymmetric (like Twitter/Instagram):

a. Mutual friendship

CREATE TABLE friendships (
    user_id INT REFERENCES users(id),
    friend_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, friend_id),
    CHECK (user_id < friend_id)
);

Use CHECK (user_id < friend_id) to avoid duplication (A–B is same as B–A)

b. Asymmetric follow (like Twitter)

CREATE TABLE follows (
    follower_id INT REFERENCES users(id),
    followee_id INT REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, followee_id)
);

📝 2. Posts (Status Updates, Images, etc.)

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Add media tables later (images, videos, etc.).

💬 3. Comments and Likes

Comments:

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id),
    user_id INT REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Likes:

CREATE TABLE likes (
    user_id INT REFERENCES users(id),
    post_id INT REFERENCES posts(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id)
);

🔔 4. Notifications (Optional)

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    recipient_id INT REFERENCES users(id),
    sender_id INT REFERENCES users(id),
    type VARCHAR(20), -- e.g., 'like', 'comment', 'follow'
    reference_id INT, -- e.g., post_id or comment_id
    seen BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📥 5. Messages (DMs)

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    sender_id INT REFERENCES users(id),
    receiver_id INT REFERENCES users(id),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

📸 6. Media (Optional)

CREATE TABLE media (
    id SERIAL PRIMARY KEY,
    post_id INT REFERENCES posts(id),
    url TEXT NOT NULL,
    media_type VARCHAR(20) -- 'image', 'video', etc.
);

🔍 7. Indexes to Add

• Indexes on foreign keys for joins and lookups
• Composite index on posts(user_id, created_at) for user feeds
• Index on follows(followee_id) for getting followers efficiently

---

🧠 Additional Considerations

Feature	Strategy
News Feed	Precompute or generate on-the-fly using posts from followees
Scaling	Sharding by user_id or use message queues for async fan-out
Privacy Settings	Add fields to users and/or posts
Hashtags, Mentions	Use full-text search or separate tables for tagging