How can I build a voting system to support multiple types of objects to vote on?

Question

I'm really looking for something very similar to the way SO is setup where a few different kinds of things can be voted on (questions AND answers). What kind of DB schema, generally, could I use to support voting on many different kinds of objects?

Would I have a single Vote table that would have references to other objects in the database? Or do I have to have or should have a separate vote table for each of the objects I would like to vote on.

Answer 1

Kyle,

it's a bit hard to understand what exactly you need...but here's my 2 cents:

I assume you want for each vote to store when it was voted, by whom (maybe IP address or user name), I would go for a single Voting table solution. I also assume that when you query an entity from the DB (question, answer, etc.), you also want to join this table, and it's not likely to have a query solely on the voting table.

in this case, you can use 2 columns on the voting table - one for the type of object that was voted, and one to hold the id of that object. that way you can join the voting table to any other query by specifying the type of the object.

I think managing all your votes in a single table will make your domain simpler then spreading it and creating a voting table for each entity. in this single voting table solution you only need to maintain the list of types of entities (probably using a small dictionary table).

there is also a consideration of performance. if you expect millions of votes, that single table will grow much more quickly than a set of separate tables. it might be a consideration against it. also take care to not make it a bottleneck if there are many concurrent read/writes to it.