Web application with relational database - best practice?

Question

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.

Answer 1

You should separate the model, view and controller.

For the database layer (model), I would make classes for Tweet and People. And make a join table for Tags (two columns, tweetId and userId) and another for Likes (again, two columns for tweetId and userId). Things like Post type and status might not need another table since they are just look-up-values.
Things that you need to sort and filter by are best if separated out into their own column so that you can search them in the database without needing to parse or index the "additional data". Usually, database calls is where you should be the best optimized and scaled. Don't just slam everything into one column and parse on the client, when you can leverage the database's power too.

Then when you load the data into your application (controller/view), you can pass and join them around however you like, such as using a map with key values. It just depends on your preferred framework.