implementing Posts, comments, and likes in DB

Question

I am trying to model a DB in Postgresql after a social media platform such as twitter and Instagram.

I have the following requirements:

1. A user can create a Post
2. A User can Like a Post
3. A user can comment on a Post
4. A user can comment on another users comment (Reply to a comment)
5. A user can like a comment

Now I am aware that we can have deeply nested comments if users keep replying to other users in the form of comments. I came up with a a few self-referencing tables that all inherit from a common Entity table. Here is my implemntation so far:

^^ I am aware that it is possible to "like" another persons "Like" or comment on a "Like" which is not a requirement. To prevent this I am thinking I can enforce these constraints at the application code level. Its always better to leave options open incase we might want to implement them in the future right?

My Question is, is this a good DB implementation? Are there use cases and pitfalls I might run into that I am not seeing? Does the design fit the use cases?

Answer 1

Your basic schema structure is probably usable for the basic use cases you were mentioning. I am just missing a connection between comment and post (which comment belongs to which post). You could also argue that comment and post are the same type of object, just distinguished by the relation to another post they have (or not have). Also - more importantly:

Nowadays you should consider using a graph database for modeling the social media domain. As you can see in your schema most of the data are links between the tables and relational databases are actually not the best at highly linked data. This is due to the fact that SQL queries will likely end up including a lot of joins which can become a performance problem once your graph reaches a certain size and depth.

It is also possible to combine the use of a relational database (or nosqldb) with a graph database, in which you only model the link network inside the graph and more table oriented data in the regular database.

For a popular example of a graph database see this.

For more information on why graph databases are better than relational databases at modeling graphs, read this and this.