I will produce a voting system for any web application and question what the easiest way is always to keep votes within the (SQL) database.

The voting product is similiar towards the among StackOverflow. I'm pondering if I ought to keep up and lower votes in various tables. This way it's simpler to count all up votes resp. lower votes. However I must query two tables to locate all votes to have an user or chosen item.

An alternate could be one table having a boolean area that identifies if the election is definitely an up or lower election. However I guess counting up or lower votes is very slow (if you have lots of votes), as well as an index on the boolean area (so far as I understand) doesn't make lots of sense.

How does one produce the database structure? A couple of tables?

In case your votes are simply up/lower then you may create a votes table connecting towards the posts and getting a value of just one or -1 (up / lower). By doing this you are able to sum in one go.

Concerning the comments, we found the answer that best suits to Zardoz

He doesn't wish to always count votes and requires just as much particulars as you possibly can. Therefore the option would be a mixture of both.

  1. Adding an integer area within the considered table to keep election counts (make certain there will not be overflows).
  2. Create additional tables to log the votes (user, publish, date, up/lower, etc.)

I would suggest to make use of triggers to instantly update the 'vote count field' when placing/removing/upgrading a election within the log table.

You may need a link table between customers and also the organizations that are being chosen on, I'd have believed. This will help you to see which customers have previously chosen and stop them from posting further votes. The table can record inside a boolean whether it's an up or lower election.

I'd advise storing within the chosen entity a present election tally area to help ease querying. The saving in dimensions could be minimal should you overlooked this.