I am creating an app that has a user, question, answer, comment, and voting tables. Not sure if it's good decision, but I've decided to make the voting table into a join table containing IDs of all the other tables instead of having every other table have a vote_count column because votes would belong-to every other table.
The voting table look like this-
CREATE TABLE vote (
"questionVoteCount" SERIAL,
"answerVoteCount" SERIAL,
"commentVoteCount" SERIAL,
"accountId" INTEGER REFERENCES account(id),
"questionId" INTEGER REFERENCES question(id),
"answerId" INTEGER REFERENCES answer(id),
"commentId" INTEGER REFERENCES comment(id),
PRIMARY KEY ("questionVoteCount", "answerVoteCount",
"commentVoteCount")
);
My model look like this-
class Vote {
constructor({
questionVoteCount,
answerVoteCount,
commentVoteCount,
accountId,
questionId,
answerId,
commentId
} = {}) {
this.questionVoteCount =
this.questionVoteCount || VOTE_DEFAULTS.questionVoteCount
this.answerVoteCount = this.answerVoteCount || VOTE_DEFAULTS.answerVoteCount
this.commentVoteCount =
this.commentVoteCount || VOTE_DEFAULTS.commentVoteCount
this.accountId = accountId || VOTE_DEFAULTS.accountId
this.questionId = questionId || VOTE_DEFAULTS.questionId
this.answerId = answerId || VOTE_DEFAULTS.answerId
this.commentId = commentId || VOTE_DEFAULTS.commentId
}
static upVoteQuestion({ accountId, questionId }) {
return new Promise((resolve, reject) => {
pool.query(
`UPDATE vote SET "questionVoteCount" =
"questionVoteCount" + 1 WHERE
"questionId" = $1 AND "accountId" =
$2`,
[questionId, accountId],
(err, res) => {
if (err) return reject(err)
resolve()
}
)
})
}
I wanted each question/answer/comment to have a vote count, and a user posting on the voting route would increment or decrement the votes of any of the above. How can I go about doing that? I have a feeling that I've made some error with the voting table itself. Should I have stuck with my original idea of having a vote_count column in each table?
UPDATED TABLE- thanks to alfasin
CREATE TABLE vote (
"questionVoteCount" INTEGER DEFAULT 0 NOT NULL,
"answerVoteCount" INTEGER DEFAULT 0 NOT NULL,
"commentVoteCount" INTEGER DEFAULT 0 NOT NULL,
"accountId" INTEGER REFERENCES account(id),
"questionId" INTEGER REFERENCES question(id),
"answerId" INTEGER REFERENCES answer(id),
"commentId" INTEGER REFERENCES comment(id),
);
Instead of running an ''UPDATE' statement, 'INSERT'-ing "voteCount" with 1 for upvote and -1 for downvote worked for me.
Now I can run a 'SELECT SUM("voteCount")' to get all the votes for questions, answers, comments, users and whatever.