Relational Database Modeling — LinkedIn Schema Design — Part II
Hi All, welcome back to the part II of the relational database modeling. In the last blog we discussed how to design a model schema with LinkedIn as an example, If you haven’t read it yet, please check the link below:
https://aarthikannan2103.medium.com/relational-database-modeling-linkedin-schema-design-ce234c775543
Now let’s continue with our features and continue the design.
4. An user may give or receive recommendations from a colleague.
Here a user can send one particular recommendation to one other user.
Can we add this recommendation to the user entity?
A particular user can send a recommendation and receive a recommendation. Since this is a list it doesn’t make sense to add it to a user entity. We can go ahead and create an entity for recommendation.
A particular recommendation will just have one sender id and one receiver id. This is a one-to-one relationship and also this is already captured in this table. So we no need any mapping table for the user and recommendation entity.
5. A user can send connection requests to other users. Unless the recipient approves the connection request, the sender will just be a follower. Once the connection request is approved, both sender and receiver become a follower of each other.
Here we have a connection entity that has connection_sender_id and connection_receiver_id.
If “A” sends a request to “B”, then connection_sender_id will be “A”, and connection_receiver_id will be “B” and if “B” accepts the request of “A”, the connection_sender_id will be “B” and connection_receiver_id will be “A”.
Our table looks like this,
Now if we want to query all the followers of “A”, then,
select connection_sender_id from connection where connection_receiver_id=”A”,
AND If we want to query all the users that “A” follows, then,
select connection_receiver_id from connection where connection_sender_id=”A”.
But this causes redundancy in the collection, it is better to have a flag that denotes whether the receiver accepts the request. If so, both the user will be mutual followers. If not then only the sender follows receiver and not vice versa.
Now if we want to query all the followers of “A”, then,
select connection_sender_id from connection where connection_receiver_id=”A” and select connection_receiver_id from connection where connection_sender_id=”A” and is_mutual=“yes”,
AND If we want to query all the users that “A” follows, then,
select connection_receiver_id from connection where connection_sender_id=”A” and select connection_sender_id from connection where connection_receiver_id=”A” and is_mutual=”yes”.
6. A user should be able to write a Post.
A user should be able to comment on a post or reply to a comment.
A user should be able to like a post or a comment.
Here we have an entity Post. Every Post will have only one user but an User can have multiple Post. So this is one-to-may relationship. This doesn’t need a separate mapping table.
We have other Entity here Comment.
comment has a user who created it. User can comment either in Post or an other comment.
So, here we have to have a track of which post/comment it belongs to.
If it belongs to a post, post_id will be id of the particular post and comment_id will be null (or)
If it belongs to comment, comment_id will be the immediate parent comment_id and post_id will be null.
This is okay, but the best practice is to have one common parent entity_id (post/comment) and mark the type as enum (To denote whether it belongs to the comment/post).
Now the last feature is Like Entity, same as comment, this has an user who liked a post/comment. As we discussed for comment Entity, we can have a common entity_id and entity_type which points either to a comment/post.
Hurray!! we did this. Now let’s combine everything and see what we created.!!!!!!!
Thanks for reading :) . Will meet again in the next blog soon. 😍
