CODEWITHSUNDEEP
sqlpostgresqldatabase-designrelational-databaseentity-relationship
john
john

Reputation: 1075

implementing Posts, comments, and likes in DB

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:

enter image description here

^^ 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?

Upvotes: 9

Views: 9789

Answers (2)

josephnw
josephnw

Reputation: 1

Current implementation is good enough. Points to consider:

  • Separation of post's likes and comment's likes may lead to more clarity in development.
  • Consider deeply nested comments for performance issues in relational database (it may require recursion and or multiple joins). You may consider to limit the comments to depth of 2 (example of Facebook and Youtube).
  • More attributes: user password, entity's creation date, etc.

Upvotes: 0

Oswin Noetzelmann
Oswin Noetzelmann

Reputation: 9555

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.

Upvotes: 5

Related Questions