I'm writing what will be an intranet application, and one of its features is roughly analogous to content voting – not unlike what SO, Amazon, and many other sites do.
Assuming each votable piece of content has a unique ID, and each user (they're authenticated) has a unique ID, the easiest way would seem to be to have a "votes" table…
ContentID int UserID int VoteValue int
But this creates one row per vote – with millions of pieces of content and tens of thousands of users, that table's gonna be huge huge huge. Is this the best way to do it? I mean, if an int takes 4 bytes, each row takes 12 bytes. If a million pieces of content get a hundred votes, that's 400MB+ in storage, yeah? Seems… like a lot :). Even if the VoteValue is a tinyint (which is probably fine) and only 1 byte, that's still a couple hundred megabytes in the table. I mean sheesh.
Is there a smarter way? Should I store this "votes" table in a separate database (ignoring potential data integrity issues) to partition it from the "main" data in terms of storage and performance?
(I do realize that in today's world 400MB ain't a ton – but it seems like a LOT just to store votes, yeah?)