Linkedin Database Design

Introduction

LinkedIn stands out as a professional networking platform with a user base of over 750 million professionals worldwide. It handles an enormous amount of user data, including profiles, connections, job postings, and much more. In this blog post, I'll design a database for Linkedin and understand the various entities and relationships between them.

To keep the scope of this blog limited, here are the requirements that we want our database to satisfy:

1. Users can create their profile with information like name, contact information, headline, summary, education, work experience and different skills.

2. Users can send connection requests to other users, or follow them only. They should also be able to accept or reject the connection request.

3. Users can create posts, and like and comment on others' posts.

You can view the complete SQL code to create the below design along with the ER diagram on my github repo here.

User Profile

Each user will have some basic information like name, current location, headline and summary. Along with this, each user can have multiple work experiences and multiple education details. Considering these things, we can have the following tables:

• UserProfiles:
  ▸ user_id (Primary Key)
  ▸ first_name
  ▸ middle_name
  ▸ last_name
  ▸ contact_information_id (Foreign Key to the ContactInformation table)
  ▸ headline
  ▸ location
  ▸ about_section

• ContactInformation:
  ▸ contact_information_id (Primary Key)
  ▸ email
  ▸ email_visibility
  ▸ phone_no
  ▸ phone_no_visibility
  ▸ website
  ▸ website_visibility
  Visibility can be one of "PUBLIC", "PRIVATE" or "CONNECTIONS_ONLY".

• Skills:
  ▸ skill_id (Primary Key)
  ▸ skill_name

• EducationDetails:
  ▸ education_id (Primary Key)
  ▸ school_id (Foreign Key to the Schools table)
  ▸ degree
  ▸ field_of_study
  ▸ start_date
  ▸ end_date
  ▸ grade
  ▸ description

• Schools:
  ▸ school_id (Primary Key)
  ▸ school_name
  ▸ location
  ▸ website

• UserEducationDetails:
  ▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
  ▸ education_id (Primary Key, Foreign Key to the EducationDetails table)
  This table records the user and their corresponding education details.

• EducationSkills:
  ▸ education_id (Primary Key, Foreign Key to the EducationDetails table)
  ▸ skill_id (Primary Key, Foreign Key to the Skills table)
  Associates skills with education.

• UserSkills:
  ▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
  ▸ skill_id (Primary Key, Foreign Key to the Skills table)
  Associate users with the skills.

• Companies:
  ▸ company_id (Primary Key)
  ▸ company_name
  ▸ company_website
  ▸ about_company

• Experiences:
  ▸ experience_id (Primary Key)
  ▸ company_id (Foreign Key to the Companies table)
  ▸ profile_headline
  ▸ employment_type
  ▸ start_date
  ▸ end_date
  ▸ location_type
  ▸ employment_location
  ▸ is_current_role
  ▸ employment_industry
  ▸ description
  Here, employment_type can be one of "FULL-TIME", "PART-TIME", "SELF-EMPLOYED", "FREELANCE", "INTERNSHIP" or "TRAINEE", and location_type can be one of "ON-SITE", "HYBRID" or "REMOTE".

• ExperienceSkills:
  ▸ experience_id (Primary Key, Foreign Key to the Experiences table)
  ▸ skill_id (Primary Key, Foreign Key to the Skills table)
  Associates experience with skills.

• UserExperience:
  ▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
  ▸ experience_id (Primary Key, Foreign Key to the Experiences table)
  Associates users with experiences.

This completes a user's profile.

Connection Requests

We need to record who sent a connection request to whom, along with its status, and the followers of a user. Here are the tables to record this:

• Connections:
  ▸ connection_id (Primary Key)
  ▸ request_sent_by (Foreign Key to the UserProfiles table)
  ▸ request_sent_to (Foreign Key to the UserProfiles table)
  ▸ request_status
  request_status can be one of "CONNECTED" or "PENDING".

• Followers:
  ▸ followed_by (Primary Key, Foreign Key to the UserProfiles table)
  ▸ following (Primary Key, Foreign Key to the UserProfiles table)

These 2 tables can record information for connections and followers.

Posts and Comments

A user can create a post, and like and comment on others' posts. We can have the following tables for this:

• Posts:
  ▸ post_id (Primary Key)
  ▸ user_id (Foreign Key to the UserProfiles table)
  ▸ description
  ▸ created_at
  ▸ updated_at

• PostReactions:
  ▸ post_id (Primary Key, Foreign Key to the Posts table)
  ▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
  ▸ reaction
  reaction can be one of '"LIKE'", "CELEBRATE", "SUPPORT", "FUNNY", "LOVE", or "INSIGHTFUL".

• Comments:
  ▸ comments_id (Primary Key)
  ▸ post_id (Foreign Key to the Posts table)
  ▸ user_id (Foreign Key to the UserProfiles table)
  ▸ description
  ▸ created_at
  ▸ updated_at

• CommentReactions:
  ▸ comments_id (Primary Key, Foreign Key to the Comments table)
  ▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
  ▸ reaction

Complete Design

Here's a complete database design for the tables mentioned above:

Conclusion

Overall, there are 18 tables in this design. This design represents only the basic functionality. A lot more features can be added to this such as:

• LinkedIn Groups

• Linkedin Pages

• Linkedin Articles

• Block a User

• Featured Section in a user's profile

and many more.

Also, this design is for the OLTP database. For analytical processing or warehousing, we would need a much more denormalized design. But that's a topic for another blog.

Thanks for reading!