I am currently designing a web application which is pretty similar to Twitter but with some unique features for my company. Technologies are Vue2, Spark framework and PostgreSQL.
I am trying to decide on how to design the ER and how to integrate the client side with my API. Let me break down the problem.
These are the details of a "tweet" which is the main component in the application:
Id,
Timestamp,
Post type (from another table),
TaggedPeople (A list of user ids from another table),
Text,
Status (from antoher table),
Likes (A list of user ids from another table),
Additional data (according to the posts type)
I need to show a bunch of these tweets in my feed, add/update/delete tweets and filter tweets by different parameters.
Currently I am torn between 3 options:
- Join query with other tables (types, users and statuses).
- Denormalizing all of the foreign keys into a JSONB column (I'm using PostgreSQL).
- Returning the data with the keys and "joining" the actual text in the client side - using preloaded data in Vuex (A state managment tool in Vue).
It will be very cool the hear from someone who is experienced with these type of web applications.
Thanks in advance.