2

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.

Improve this question
5
  • 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, 2018 at 19:14
  • Looking into multi-tier frameworks like ocsigen could be inspirational
    – Basile Starynkevitch
    Aug 23, 2018 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, 2018 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, 2018 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, 2018 at 17:09

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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