// ============================================ // LinkedIn Data Model — Complete DBML Schema // 22 Entities | FAANG Interview Level // ============================================
// ---- ENUMS ----
Enum employment_type { full_time part_time contract internship freelance self_employed }
Enum company_size { "1-10" "11-50" "51-200" "201-500" "501-1000" "1001-5000" "5001-10000" "10001+" }
Enum company_type { public private nonprofit government educational self_employed }
Enum admin_role { super_admin content_admin analyst recruiter }
Enum connection_status { pending accepted rejected withdrawn }
Enum post_type { text image video document article poll repost }
Enum post_visibility { public connections_only }
Enum reaction_type { like celebrate support love insightful funny }
Enum seniority_level { intern entry associate mid_senior director executive }
Enum application_status { applied under_review interviewing offered rejected withdrawn }
Enum job_status { active closed draft expired }
Enum message_type { text image file voice inmail system }
Enum notification_type { connection_request connection_accepted post_reaction post_comment mention profile_view job_alert endorsement recommendation_request birthday work_anniversary }
Enum relationship_type { managed_directly reported_to worked_together was_client was_mentor was_student }
Enum recommendation_status { pending accepted hidden }
Enum profile_visibility { public connections_only private }
// ---- TABLES ----
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]
vanity_url varchar(100) [unique, note: 'Custom URL slug e.g. john-doe']
headline varchar(220)
summary text [note: 'Max 2600 characters']
profile_photo_url varchar(500)
background_photo_url varchar(500)
location varchar(100)
country varchar(50)
industry varchar(100)
current_company varchar(200) [note: 'Denormalized for fast display']
connections_count int [default: 0]
followers_count int [default: 0]
is_open_to_work boolean [default: false]
is_hiring boolean [default: false]
profile_visibility profile_visibility [default: 'public']
created_at timestamp [default: now()]
last_active_at timestamp
is_verified boolean [default: false]
is_premium boolean [default: false]
Note: 'Professional identity — the core entity. Headline + current_company denormalized for search results.' }
Table work_experience { experience_id int [pk, increment] user_id int [ref: > users.user_id, not null] company_id int [ref: > companies.company_id] company_name varchar(200) [not null, note: 'Text fallback if no company page'] job_title varchar(200) [not null] employment_type employment_type location varchar(100) start_date date [not null] end_date date [note: 'NULL = current position'] is_current boolean [default: false] description text skills_used text
indexes { (user_id, is_current) (company_id, is_current) }
Note: 'Multiple positions per user. Multiple users per company. is_current drives employee counts.' }
Table education { education_id int [pk, increment] user_id int [ref: > users.user_id, not null] institution_name varchar(200) [not null] degree varchar(100) field_of_study varchar(100) grade varchar(20) activities text description text start_year int end_year int
Note: 'Academic history. Used for Alumni search and People You May Know.' }
Table companies {
company_id int [pk, increment]
name varchar(200) [not null]
vanity_url varchar(100) [unique]
logo_url varchar(500)
cover_photo_url varchar(500)
industry varchar(100)
company_size company_size
company_type company_type
headquarters varchar(100)
founded_year int
website varchar(200)
description text
specialties text
followers_count int [default: 0]
employee_count_on_linkedin int [default: 0, note: 'Denormalized from work_experience']
created_at timestamp [default: now()]
Note: 'Company Pages. employee_count is denormalized aggregate of current employees.' }
Table company_admins {
admin_id int [pk, increment]
company_id int [ref: > companies.company_id, not null]
user_id int [ref: > users.user_id, not null]
role admin_role [not null]
assigned_at timestamp [default: now()]
assigned_by int [ref: > users.user_id]
indexes { (company_id, user_id) [unique] }
Note: 'Company page administration with role-based access control.' }
Table company_followers {
follow_id int [pk, increment]
user_id int [ref: > users.user_id, not null]
company_id int [ref: > companies.company_id, not null]
followed_at timestamp [default: now()]
indexes { (user_id, company_id) [unique] } }
Table connections {
connection_id int [pk, increment]
requester_id int [ref: > users.user_id, not null]
receiver_id int [ref: > users.user_id, not null]
status connection_status [default: 'pending']
note varchar(300) [note: 'Personal note with request']
requested_at timestamp [default: now()]
responded_at timestamp
indexes { (requester_id, receiver_id) [unique] (receiver_id, status) }
Note: 'Bidirectional professional connections. Max 30K per user.' }
Table follows {
follow_id int [pk, increment]
follower_id int [ref: > users.user_id, not null]
followed_id int [ref: > users.user_id, not null]
followed_at timestamp [default: now()]
indexes { (follower_id, followed_id) [unique] }
Note: 'Unidirectional follows. Connecting auto-creates mutual follows.' }
Table skills { skill_id int [pk, increment] name varchar(100) [unique, not null] category varchar(100)
Note: 'Curated global taxonomy of 40,000+ professional skills.' }
Table user_skills {
user_skill_id int [pk, increment]
user_id int [ref: > users.user_id, not null]
skill_id int [ref: > skills.skill_id, not null]
display_order int
endorsement_count int [default: 0, note: 'Denormalized from endorsements']
is_assessment_passed boolean [default: false]
added_at timestamp [default: now()]
indexes { (user_id, skill_id) [unique] }
Note: 'Skills on a profile. Max ~50 per user.' }
Table endorsements {
endorsement_id int [pk, increment]
user_skill_id int [ref: > user_skills.user_skill_id, not null]
endorsed_by int [ref: > users.user_id, not null]
endorsed_at timestamp [default: now()]
indexes { (user_skill_id, endorsed_by) [unique] }
Note: 'One-click skill validation from connections.' }
Table recommendations {
recommendation_id int [pk, increment]
author_id int [ref: > users.user_id, not null]
recipient_id int [ref: > users.user_id, not null]
relationship relationship_type [not null]
position_at_time varchar(200)
company_at_time varchar(200)
content text [not null]
status recommendation_status [default: 'pending']
created_at timestamp [default: now()]
Note: 'Written professional testimonials. Must be accepted to be visible.' }
Table certifications { certification_id int [pk, increment] user_id int [ref: > users.user_id, not null] name varchar(200) [not null] issuing_organization varchar(200) issue_date date expiration_date date credential_id varchar(100) credential_url varchar(500) }
Table projects { project_id int [pk, increment] user_id int [ref: > users.user_id, not null] name varchar(200) [not null] description text url varchar(500) start_date date end_date date associated_experience_id int [ref: > work_experience.experience_id] }
Table posts {
post_id bigint [pk, increment]
author_id int [ref: > users.user_id, not null]
company_id int [ref: > companies.company_id, note: 'NULL for personal posts']
content text [note: 'Max 3000 characters']
post_type post_type [not null]
media_urls text [note: 'JSON array of media URLs']
visibility post_visibility [default: 'public']
original_post_id bigint [ref: > posts.post_id, note: 'For reposts']
hashtags text
impressions_count int [default: 0]
reactions_count int [default: 0]
comments_count int [default: 0]
reposts_count int [default: 0]
created_at timestamp [default: now()]
updated_at timestamp
is_deleted boolean [default: false]
indexes { (author_id, created_at) }
Note: 'News feed content. Self-referencing for reposts.' }
Table post_reactions {
reaction_id bigint [pk, increment]
post_id bigint [ref: > posts.post_id, not null]
user_id int [ref: > users.user_id, not null]
reaction_type reaction_type [not null]
reacted_at timestamp [default: now()]
indexes { (post_id, user_id) [unique] } }
Table comments {
comment_id bigint [pk, increment]
post_id bigint [ref: > posts.post_id, not null]
author_id int [ref: > users.user_id, not null]
parent_comment_id bigint [ref: > comments.comment_id, note: 'Self-referencing for replies']
content text [not null]
reactions_count int [default: 0]
created_at timestamp [default: now()]
updated_at timestamp
is_deleted boolean [default: false]
Note: 'Threaded comments. 2 levels: top-level and replies.' }
Table job_postings {
job_id int [pk, increment]
company_id int [ref: > companies.company_id, not null]
posted_by int [ref: > users.user_id, not null]
title varchar(200) [not null]
description text [not null]
location varchar(100)
is_remote boolean [default: false]
employment_type employment_type
seniority_level seniority_level
salary_min decimal
salary_max decimal
salary_currency varchar(3) [default: 'USD']
required_skills text [note: 'JSON array of skill names']
easy_apply_enabled boolean [default: true]
application_url varchar(500)
applicant_count int [default: 0]
status job_status [default: 'active']
posted_at timestamp [default: now()]
closes_at timestamp
indexes { (company_id, status) (status, posted_at) }
Note: 'Job marketplace listings. Primary monetization engine.' }
Table job_applications {
application_id int [pk, increment]
job_id int [ref: > job_postings.job_id, not null]
applicant_id int [ref: > users.user_id, not null]
resume_url varchar(500)
cover_letter text
status application_status [default: 'applied']
applied_at timestamp [default: now()]
status_updated_at timestamp
recruiter_notes text
indexes { (job_id, applicant_id) [unique] (applicant_id, applied_at) }
Note: 'Application tracking. One application per user per job.' }
Table conversations {
conversation_id bigint [pk, increment]
is_group boolean [default: false]
title varchar(200) [note: 'Group conversation name']
created_by int [ref: > users.user_id]
created_at timestamp [default: now()]
participant_count int [default: 2]
}
Table messages {
message_id bigint [pk, increment]
conversation_id bigint [ref: > conversations.conversation_id, not null]
sender_id int [ref: > users.user_id, not null]
content text
message_type message_type [default: 'text']
attachment_url varchar(500)
is_inmail boolean [default: false]
is_read boolean [default: false]
sent_at timestamp [default: now()]
indexes { (conversation_id, sent_at) } }
Table notifications {
notification_id bigint [pk, increment]
user_id int [ref: > users.user_id, not null]
type notification_type [not null]
actor_id int [ref: > users.user_id]
entity_type varchar(50)
entity_id bigint
content varchar(500)
is_read boolean [default: false]
created_at timestamp [default: now()]
indexes { (user_id, is_read, created_at) }
Note: 'Polymorphic notifications. entity_type + entity_id reference any entity.' }
// ---- TABLE GROUPS ----
TableGroup profile_domain { users work_experience education certifications projects }
TableGroup network_domain { connections follows skills user_skills endorsements recommendations }
TableGroup company_domain { companies company_admins company_followers }
TableGroup content_domain { posts post_reactions comments }
TableGroup jobs_domain { job_postings job_applications }
TableGroup messaging_domain { conversations messages notifications }