CODEWITHSUNDEEP
mysqlruby-on-railsdatabasedatabase-designvoting
Inc1982
Inc1982

Reputation: 1975

How to structure a categorized voting database?

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!

Upvotes: 0

Views: 282

Answers (2)

Gustav Bertram
Gustav Bertram

Reputation: 14911

I think you should got for option 2.

You need to create a vote model anyway, since you'll probably want to limit users to one vote on each item.

If you have performance issues later on, you can always cache the number of votes in an item, topic or category.

How you update those numbers should be carefully considered. A trigger on votes that auto-updates all the numbers above might cause too many write operations. Another way may be to run a statistics stored procedure periodically.

Anyway, the real point is - don't optimize until you know there's a problem.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

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.

Upvotes: 1

Related Questions