Large Voting Schema: How would you do it? [closed]

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 9 years ago.

Improve this question

Ok, so I've been thinking a lot about various sites like Reddit.com. There are thousands of posts, and for each post thousands of comments and on top of all that there are votes which are tracked by user for all comments and posts.

So, considering articles, comments, and article votes (don't really care about comment votes) the way I know how to do it would be 3 tables:

Articles: id, value, username, totalvotes, other relevent data

Comments: id, articleid, value, username, other relevent data

votes: id, articleid, username, votevalue (+1,-1), other relevent data

So basically a one to many relation between Articles and comments/votes. Here are the questions I have in regards to this:

1. Is this the right way to do this?

2. Wouldn't it be extremely slow to tally all the votes by iterating through the whole votes table lo开发者_开发知识库oking for the right article?

3. Would you keep a running total going or just query the whole votes table everytime (question 2).

4. My other idea was to make tables on the fly for each article, but that might be overkill. Thoughts?

---

1. yes
2. no - indexing is your friend
3. no - that is denormalized and would be hard to maintain
4. oooh.. no

---

One answer : Cache !

As Randy said this is not a big deal to have a lot of datas if your tables are well indexed, but it can be too slow for a good user experience. So consider to cache everything you can (like number of responses) and update this only when a new comment is made. Also I strongly advice you to fetch all related datas that aren't in cache by ajax and not directely at page load.

TLDR :

1. Yes

2. No

3. Cache

4. Hell no