// ============================================ // FACEBOOK DATA MODEL - DBML FORMAT // Use at: https://dbdiagram.io/ // ============================================

// ============================================ // CORE ENTITY: USERS // ============================================

Table users { user_id int [pk, increment] email varchar(100) [unique, not null] password_hash varchar(255) [not null] first_name varchar(50) [not null] last_name varchar(50) [not null] username varchar(50) [unique] date_of_birth date [not null, note: 'Must be 13+'] gender varchar(20) profile_picture_url varchar(500) cover_photo_url varchar(500) bio text location varchar(100) hometown varchar(100) relationship_status varchar(50) created_at timestamp [default: CURRENT_TIMESTAMP] is_active boolean [default: true] is_verified boolean [default: false] friends_count int [default: 0, note: 'Denormalized']

Indexes { email username (first_name, last_name) }

Note: 'Platform users with rich profile information' }

// ============================================ // FRIENDSHIPS (Bidirectional) // ============================================

Table friendships { friendship_id int [pk, increment] user_id_1 int [not null, ref: > users.user_id] user_id_2 int [not null, ref: > users.user_id] created_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { user_id_1 user_id_2 (user_id_1, user_id_2) [unique, note: 'Prevent duplicate friendships'] }

Note: 'Bidirectional friendships - mutual connections' }

Table friend_requests { request_id int [pk, increment] sender_id int [not null, ref: > users.user_id] receiver_id int [not null, ref: > users.user_id] status varchar(20) [default: 'pending', note: 'pending, accepted, rejected'] requested_at timestamp [default: CURRENT_TIMESTAMP] responded_at timestamp [null]

Indexes { sender_id receiver_id status (sender_id, receiver_id) [unique, note: 'One request per pair'] }

Note: 'Friend request management - accepted creates friendship' }

// ============================================ // CONTENT: POSTS // ============================================

Table posts { post_id bigint [pk, increment] user_id int [not null, ref: > users.user_id] group_id int [ref: > groups.group_id, note: 'If posted in group'] page_id int [ref: > pages.page_id, note: 'If posted on page'] content text [not null] post_type varchar(20) [default: 'status', note: 'status, photo, video, link, share'] privacy_level varchar(20) [default: 'friends', note: 'public, friends, custom'] location varchar(100) feeling varchar(50) created_at timestamp [default: CURRENT_TIMESTAMP] updated_at timestamp [default: CURRENT_TIMESTAMP] is_deleted boolean [default: false] reactions_count int [default: 0] comments_count int [default: 0] shares_count int [default: 0]

Indexes { user_id group_id page_id created_at (user_id, created_at) [note: 'User timeline'] }

Note: 'User-generated content on timelines, groups, and pages' }

// ============================================ // ENGAGEMENT: REACTIONS // ============================================

Table reactions { reaction_id bigint [pk, increment] user_id int [not null, ref: > users.user_id] post_id bigint [not null, ref: > posts.post_id] reaction_type varchar(20) [not null, note: 'like, love, haha, wow, sad, angry'] created_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { user_id post_id (user_id, post_id) [unique, note: 'One reaction per user per post'] }

Note: 'Facebook reactions - 6 types: Like, Love, Haha, Wow, Sad, Angry' }

// ============================================ // ENGAGEMENT: COMMENTS // ============================================

Table comments { comment_id bigint [pk, increment] post_id bigint [not null, ref: > posts.post_id] user_id int [not null, ref: > users.user_id] parent_comment_id bigint [ref: > comments.comment_id, note: 'For nested replies'] content text [not null] created_at timestamp [default: CURRENT_TIMESTAMP] updated_at timestamp [default: CURRENT_TIMESTAMP] is_deleted boolean [default: false] reactions_count int [default: 0, note: 'Comments can be reacted to']

Indexes { post_id user_id parent_comment_id }

Note: 'Nested comments - self-referencing for threaded discussions' }

// ============================================ // ENGAGEMENT: SHARES // ============================================

Table shares { share_id bigint [pk, increment] user_id int [not null, ref: > users.user_id] post_id bigint [not null, ref: > posts.post_id] shared_to_group_id int [ref: > groups.group_id] share_comment text shared_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { user_id post_id }

Note: 'Post shares to timeline or groups' }

// ============================================ // COMMUNITIES: GROUPS // ============================================

Table groups { group_id int [pk, increment] name varchar(200) [not null] description text privacy_type varchar(20) [default: 'public', note: 'public, private, secret'] created_by int [not null, ref: > users.user_id] cover_photo_url varchar(500) created_at timestamp [default: CURRENT_TIMESTAMP] members_count int [default: 0]

Indexes { name created_by }

Note: 'Communities and interest-based groups' }

Table group_members { membership_id int [pk, increment] group_id int [not null, ref: > groups.group_id] user_id int [not null, ref: > users.user_id] role varchar(20) [default: 'member', note: 'admin, moderator, member'] joined_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { group_id user_id (group_id, user_id) [unique] }

Note: 'Group membership with roles' }

// ============================================ // BUSINESS: PAGES // ============================================

Table pages { page_id int [pk, increment] name varchar(200) [not null] category varchar(100) description text profile_picture_url varchar(500) cover_photo_url varchar(500) website varchar(200) created_at timestamp [default: CURRENT_TIMESTAMP] followers_count int [default: 0]

Indexes { name category }

Note: 'Business and brand pages' }

Table page_followers { follower_id int [pk, increment] page_id int [not null, ref: > pages.page_id] user_id int [not null, ref: > users.user_id] followed_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { page_id user_id (page_id, user_id) [unique] }

Note: 'Users follow pages (not friend)' }

// ============================================ // EVENTS // ============================================

Table events { event_id int [pk, increment] name varchar(200) [not null] description text location varchar(200) start_time datetime [not null] end_time datetime created_by int [not null, ref: > users.user_id] group_id int [ref: > groups.group_id] page_id int [ref: > pages.page_id] privacy_type varchar(20) [default: 'public'] cover_photo_url varchar(500) created_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { created_by group_id page_id start_time }

Note: 'Social events and gatherings' }

Table event_attendees { attendee_id int [pk, increment] event_id int [not null, ref: > events.event_id] user_id int [not null, ref: > users.user_id] rsvp_status varchar(20) [default: 'interested', note: 'going, interested, not_going'] responded_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { event_id user_id (event_id, user_id) [unique] }

Note: 'Event RSVPs with status' }

// ============================================ // MEDIA: PHOTOS // ============================================

Table photo_albums { album_id int [pk, increment] user_id int [not null, ref: > users.user_id] name varchar(200) [not null] description text privacy_level varchar(20) [default: 'friends'] created_at timestamp [default: CURRENT_TIMESTAMP] photos_count int [default: 0]

Indexes { user_id }

Note: 'Photo collections/albums' }

Table photos { photo_id bigint [pk, increment] user_id int [not null, ref: > users.user_id] album_id int [ref: > photo_albums.album_id] post_id bigint [ref: > posts.post_id] photo_url varchar(500) [not null] caption text created_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { user_id album_id post_id }

Note: 'Individual photos' }

Table photo_tags { tag_id bigint [pk, increment] photo_id bigint [not null, ref: > photos.photo_id] user_id int [not null, ref: > users.user_id] created_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { photo_id user_id (photo_id, user_id) [unique] }

Note: 'People tagged in photos' }

// ============================================ // MESSAGING // ============================================

Table conversations { conversation_id bigint [pk, increment] is_group_chat boolean [default: false] name varchar(200) [note: 'Optional group chat name'] created_at timestamp [default: CURRENT_TIMESTAMP]

Note: 'Message threads (1-on-1 or group)' }

Table conversation_participants { participant_id bigint [pk, increment] conversation_id bigint [not null, ref: > conversations.conversation_id] user_id int [not null, ref: > users.user_id] joined_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { conversation_id user_id (conversation_id, user_id) [unique] }

Note: 'Users in conversations' }

Table messages { message_id bigint [pk, increment] conversation_id bigint [not null, ref: > conversations.conversation_id] sender_id int [not null, ref: > users.user_id] content text [not null] sent_at timestamp [default: CURRENT_TIMESTAMP] is_read boolean [default: false]

Indexes { conversation_id sender_id sent_at }

Note: 'Individual messages in conversations' }

// ============================================ // NOTIFICATIONS // ============================================

Table notifications { notification_id bigint [pk, increment] user_id int [not null, ref: > users.user_id] type varchar(50) [not null, note: 'friend_request, post_reaction, comment, event_invite, etc.'] actor_user_id int [ref: > users.user_id] related_id bigint [note: 'ID of related entity'] is_read boolean [default: false] created_at timestamp [default: CURRENT_TIMESTAMP]

Indexes { user_id is_read created_at }

Note: 'Activity notifications' }

// ============================================ // RELATIONSHIP SUMMARY // ============================================

// Bidirectional Relationships: // - users <-> users (via friendships)

// One-to-Many: // - users -> posts // - users -> groups (created_by) // - groups -> posts // - pages -> posts // - posts -> comments // - comments -> comments (nested)

// Many-to-Many: // - users <-> groups (via group_members) // - users <-> pages (via page_followers) // - users <-> events (via event_attendees) // - users <-> posts (via reactions) // - users <-> photos (via photo_tags) // - users <-> conversations (via conversation_participants)

// ============================================ // KEY FEATURES // ============================================

// 1. Bidirectional Friendships (unlike Twitter's follow) // 2. Friend Requests (pending/accepted/rejected) // 3. Multiple Reaction Types (6 types) // 4. Nested Comments (self-referencing) // 5. Groups with Roles (admin/moderator/member) // 6. Pages (business presence) // 7. Events with RSVPs // 8. Photo Albums and Tagging // 9. Group Chats (conversations) // 10. Privacy Levels (public/friends/custom)