How to structure a categorized voting database?

Question

I have a question about how to structure a DB. I have a reddit'esque voting system. Items can get votes. But each item belongs to a topic and each topic a category. While only items can get votes I'd like to be able to access the # of votes within a topic and within a category as well. Any suggestions on how to accomplish this?

I see 4 main ways of doing this:

1. De-normalize the votes and store the votes inside an attribute in the item table, the topic table, and the category table. I would then need to update all 3 whenever a vote / downvote occurs.
2. Create a separate 'vote' model. Votes belong to items, items to topics, and topics to categories. Then I can just query number of votes through the chain whenever I need to access anything.
3. Just have items and votes. Items would have a category and topic attribute.. then I'd query for items within a topic and count the votes on them..
4. Learn to use a NoSQL db system.

Extra info: I'm using Rails and I only really know MYSQL at the moment. Is this a time I should learn something like Mongo? Can this only really be accomplished with Hadoop? Can I accomplish this in MySQL. Thanks!

Answer 1

Create a separate 'vote' model. Votes belong to items, items to topics, and topics to categories. Then I can just query number of votes through the chain whenever I need to access anything.

That's the most flexible way to do what you're talking about.

Learn to use a NoSQL db system.

Not for your current project.

Is this a time I should learn something like Mongo?

No.

Can this only really be accomplished with Hadoop?

No. Any SQL database can do this. Whether any SQL database can manage whatever you're planning is a different question. Different platforms scale differently.

Can I accomplish this in MySQL.

Yes, easily.