I'm currently designing a project and I need professional advice from DBA.
My project will feature voting system somewhat similar to the one used in stack exchage websites. I have users and content pieces and users can vote for content pieces they like or don't. Note, that I will have vote up/down option on the feed list, so if I load 30 content pieces I will need to also load the data of user's votes on every piece, since the up/down button should be highlighed if user already has voted for a specific piece. In other words, I expect big load on the votes
table. I was thinking of the basic structure like this:
Table users (user_id, ...)
, table content (content_id, ...)
, table votes (vote_id, user_id, content_id, datetime, vote)
. However, I have doubts about this design.
Let's say I have 10k users and 1k content pieces. That's up to 10 million records in table votes
. If I start thinking about scaling it up, I can imagine a big problem. Content doesn't go anywhere, old votes as well, so the longer website runs, the more records will be in the table and the slower it will work.
Let's say in some years I will have 100k users and 20k content pieces. That's up to 2 billion records. I understand that not every user will vote on every content piece, but the problem however is clear - there is a limit to that design (by limit I mean that select query will be slow when the amount of rows will reach some point).
So my questions are:
- Is there really a limit to that design and if yes, how can it be dealt with?
1.1. If the select query on
votes
table will be getting slower, what can I do to speed it up? - Is there a better way to design this kind of relations?
- How do I cache that data? Or is that even needed with proper indexing?
- What kind of indexes would you recommend for the
votes
table? Am I correct that I need a simple double-field index (user_id
,content_id
)? - Most of the load will go on recent content pieces, maybe I should create something like
recent_votes
table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?
I really would like to do things right from the start so in a few years I won't end up with a slow website. Thank you for your time.