Linkedin Database Design
Published: 2023-03-30 06:32:12

<h2 id="heading-introduction">Introduction</h2>
<p>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.</p>
<p>To keep the scope of this blog limited, here are the requirements that we want our database to satisfy:</p>
<ol>
<li><p>Users can <strong>create their profile</strong> with information like name, contact information, headline, summary, education, work experience and different skills.</p>
</li>
<li><p>Users can <strong>send connection requests</strong> to other users, or <strong>follow them</strong> only. They should also be able to <strong>accept or reject</strong> the connection request.</p>
</li>
<li><p>Users can <strong>create posts</strong>, and <strong>like</strong> and <strong>comment</strong> on others' posts.</p>
</li>
</ol>
<blockquote>
<p>You can view the complete SQL code to create the below design along with the ER diagram on my github repo <a target="_blank" href="https://github.com/Aditya-Gupta1/data-engineering-projects/tree/linkedin-db-design-oltp">here</a>.</p>
</blockquote>
<h2 id="heading-user-profile">User Profile</h2>
<p>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:</p>
<ul>
<li><p><strong>UserProfiles</strong>:<br />  ▸ user_id (Primary Key)<br />  ▸ first_name<br />  ▸ middle_name<br />  ▸ last_name<br />  ▸ contact_information_id (Foreign Key to the <em>ContactInformation</em> table)<br />  ▸ headline<br />  ▸ location<br />  ▸ about_section</p>
</li>
<li><p><strong>ContactInformation</strong>:<br />  ▸ contact_information_id (Primary Key)<br />  ▸ email<br />  ▸ email_visibility<br />  ▸ phone_no<br />  ▸ phone_no_visibility<br />  ▸ website<br />  ▸ website_visibility<br />  Visibility can be one of <em>"PUBLIC", "PRIVATE" or "CONNECTIONS_ONLY".</em></p>
</li>
<li><p><strong>Skills</strong>:<br />  ▸ skill_id (Primary Key)<br />  ▸ skill_name</p>
</li>
<li><p><strong>EducationDetails</strong>:<br />  ▸ education_id (Primary Key)<br />  ▸ school_id (Foreign Key to the <em>Schools</em> table)<br />  ▸ degree<br />  ▸ field_of_study<br />  ▸ start_date<br />  ▸ end_date<br />  ▸ grade<br />  ▸ description</p>
</li>
<li><p><strong>Schools</strong>:<br />  ▸ school_id (Primary Key)<br />  ▸ school_name<br />  ▸ location<br />  ▸ website</p>
</li>
<li><p><strong>UserEducationDetails</strong>:<br />  ▸ user_id (Primary Key, Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ education_id (Primary Key, Foreign Key to the <em>EducationDetails</em> table)<br />  This table records the user and their corresponding education details.</p>
</li>
<li><p><strong>EducationSkills</strong>:<br />  ▸ education_id (Primary Key, Foreign Key to the <em>EducationDetails</em> table)<br />  ▸ skill_id (Primary Key, Foreign Key to the <em>Skills</em> table)<br />  Associates skills with education.</p>
</li>
<li><p><strong>UserSkills</strong>:<br />  ▸ user_id (Primary Key, Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ skill_id (Primary Key, Foreign Key to the <em>Skills</em> table)<br />  Associate users with the skills.</p>
</li>
<li><p><strong>Companies</strong>:<br />  ▸ company_id (Primary Key)<br />  ▸ company_name<br />  ▸ company_website<br />  ▸ about_company</p>
</li>
<li><p><strong>Experiences</strong>:<br />  ▸ experience_id (Primary Key)<br />  ▸ company_id (Foreign Key to the <em>Companies</em> table)<br />  ▸ profile_headline<br />  ▸ employment_type<br />  ▸ start_date<br />  ▸ end_date<br />  ▸ location_type<br />  ▸ employment_location<br />  ▸ is_current_role<br />  ▸ employment_industry<br />  ▸ description<br />  Here, <code>employment_type</code> can be one of "<em>FULL-TIME", "PART-TIME", "SELF-EMPLOYED", "FREELANCE", "INTERNSHIP" or "TRAINEE",</em> and <code>location_type</code> can be one of <em>"ON-SITE", "HYBRID" or "REMOTE"</em>.</p>
</li>
<li><p><strong>ExperienceSkills</strong>:<br />  ▸ experience_id (Primary Key, Foreign Key to the <em>Experiences</em> table)<br />  ▸ skill_id (Primary Key, Foreign Key to the <em>Skills</em> table)<br />  Associates experience with skills.</p>
</li>
<li><p><strong>UserExperience</strong>:<br />  ▸ user_id (Primary Key, Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ experience_id (Primary Key, Foreign Key to the <em>Experiences</em> table)<br />  Associates users with experiences.</p>
</li>
</ul>
<p>This completes a user's profile.</p>
<h2 id="heading-connection-requests">Connection Requests</h2>
<p>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:</p>
<ul>
<li><p><strong>Connections</strong>:<br />  ▸ connection_id (Primary Key)<br />  ▸ request_sent_by (Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ request_sent_to (Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ request_status<br />  <code>request_status</code> can be one of <em>"CONNECTED" or "PENDING"</em>.</p>
</li>
<li><p><strong>Followers</strong>:<br />  ▸ followed_by (Primary Key, Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ following (Primary Key, Foreign Key to the <em>UserProfiles</em> table)</p>
</li>
</ul>
<p>These 2 tables can record information for connections and followers.</p>
<h2 id="heading-posts-and-comments">Posts and Comments</h2>
<p>A user can create a post, and like and comment on others' posts. We can have the following tables for this:</p>
<ul>
<li><p><strong>Posts</strong>:<br />  ▸ post_id (Primary Key)<br />  ▸ user_id (Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ description<br />  ▸ created_at<br />  ▸ updated_at</p>
</li>
<li><p><strong>PostReactions</strong>:<br />  ▸ post_id (Primary Key, Foreign Key to the <em>Posts</em> table)<br />  ▸ user_id (Primary Key, Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ reaction<br />  <code>reaction</code> can be one of <em>'"LIKE'", "CELEBRATE", "SUPPORT", "FUNNY", "LOVE", or "INSIGHTFUL"</em>.</p>
</li>
<li><p><strong>Comments</strong>:<br />  ▸ comments_id (Primary Key)<br />  ▸ post_id (Foreign Key to the <em>Posts</em> table)<br />  ▸ user_id (Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ description<br />  ▸ created_at<br />  ▸ updated_at</p>
</li>
<li><p><strong>CommentReactions</strong>:<br />  ▸ comments_id (Primary Key, Foreign Key to the <em>Comments</em> table)<br />  ▸ user_id (Primary Key, Foreign Key to the <em>UserProfiles</em> table)<br />  ▸ reaction</p>
</li>
</ul>
<h2 id="heading-complete-design">Complete Design</h2>
<p>Here's a complete database design for the tables mentioned above:</p>
<p><img src="https://cdn.hashnode.com/res/hashnode/image/upload/v1676631576153/3c4c27ce-fa6a-486c-9cb0-7f5e232b93bf.png" alt class="image--center mx-auto" /></p>
<h2 id="heading-conclusion">Conclusion</h2>
<p>Overall, there are <strong>18 tables</strong> in this design. This design represents only the basic functionality. A lot more features can be added to this such as:</p>
<ul>
<li><p>LinkedIn Groups</p>
</li>
<li><p>Linkedin Pages</p>
</li>
<li><p>Linkedin Articles</p>
</li>
<li><p>Block a User</p>
</li>
<li><p>Featured Section in a user's profile</p>
</li>
</ul>
<p>and many more.</p>
<p>Also, this design is for the <strong>OLTP database</strong>. For analytical processing or warehousing, we would need a much more denormalized design. But that's a topic for another blog.</p>
<p>Thanks for reading!</p>
</br><p style="color:#757CF9;">This article is published w/ <a target="_blank" href="https://scattr.io?ref=dev">Scattr  ↗️</a></p>