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:
Post type (from another table),
TaggedPeople (A list of user ids from another table),
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.

  • 1
    So, all of your options have their place. Can you talk more about what your objectives are, what you're trying to optimize for, etc? As it stands, answers have to be kind of opinion-based w/o you giving some parameters on which to judge the best option. – Paul Aug 23 '18 at 19:14
  • Looking into multi-tier frameworks like ocsigen could be inspirational – Basile Starynkevitch Aug 23 '18 at 19:25
  • Option #4 is to denormalize the tables using views. A good ORM will still allow you to map a class to a view in the database, although you can't do inserts, updates or deletes without jumping through some additional hoops. – Greg Burghardt Aug 23 '18 at 20:23
  • 4
    Suppose you denormalize the user-ids into user names and then a user changes their name. Would you then update all previous tweets with the name change, or would you accept that that person is suddenly no longer tagged in the older tweets (which could be just a few seconds old). – Bart van Ingen Schenau Aug 24 '18 at 7:33
  • As @BartvanIngenSchenau alludes to, denormalization is associated with a lot of problems. Is there a specific reason you are considering it? – JimmyJames Aug 24 '18 at 17:09

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.