Imagine a q/a site or forum similar to stackoverflow. I'm trying to determine which of these two methods would be the better design for a voting system: (note: I would also like to keep record of all a users vote decisions; and you can assume the site experiences a relatively high amount of traffic)
create a junction table named VOTE referencing USERS, and POSTS. The problem I see with this is that when a view displays a topic listing all the posts, this seems very inefficient when we want to display each post's "score", since we'd have to count N votes for M number of posts; if the forum is very large this will be a major hindrance.
create the above junction table, but also keep a score on the posts table and simply use this to alleviate the above performance issue. The problem with this is that it describes something that is already described in another table which isn't a good idea either, if im correct?
Use some nosql solution for this part of the application; however this introduces another moving part into the equation and complicates things by having two db technologies working with eachother.
(my solution doesn't have to be one of these three, if you have a fourth or even fifth it is more than welcome, thank you)
(oh, and if it matters in any way, ill be using postgres)