CODEWITHSUNDEEP
mysqlcount
Anton Molenaar
Anton Molenaar

Reputation: 9

what is the best way to store and count votes?

What is the best way to store and count votes?

I have 2 tables:

[posts]
  id
  user_id
  likes
  dislikes
  ...

[likes_dislikes]
  type // 0 if disliked, 1 if liked
  post_id      
  user_id

Is it a good way to keep track of the likes/dislikes in the 'likes_dislikes' table and also update the related columns in the 'post' table,

or is it better to only store the votes in the 'likes_dislikes' table and count the number of votes with a query each time?

Right now I have 14 colums in the 'posts' table. Is that too much?

Upvotes: 1

Views: 458

Answers (2)

kmas
kmas

Reputation: 6439

Having 14 columns in a table is not a lot.

The thing you have to keep in mind is that a good DB schema tries not to duplicate informations. I think it is the most important thing. The only things you should duplicate are the foreign IDS, that's all.

Look at this link : http://en.wikipedia.org/wiki/Database_normalization

Database normalization is the base of everything in database development.

You have to understand these normal forms.

As I don't really understand your question about your votes and user, I won't tell you what to do before you explain in a better way.

Upvotes: 3

amaster
amaster

Reputation: 2163

or is it better to only store the votes in the 'likes_dislikes' table and count the number of votes with a query each time?

Yes this would be better, first because you already have to run a query and the time to count the likes and dislikes from a relating table will not be of a noticeable difference. Secondly, as @kmas stated, normalize your database to not duplicate data. If the user_id of the posts table will match all of the user_id for the same post_id of the likes_dislikes table then I would put them all in one table, but I do not believe this to be the case here. Correct me if I am wrong, but the user_id in the likes_dislikes table is the user who voted and the user_id of the posts table is the owner of the post. Reading a database is much faster then writing to a database. If you would have to write to an extra table every time verses read from an extra table every time, I would go with the read. This will not only make your database normalized, but would also make it easier on your code.

Oh, and for the Right now I have 14 columns in the 'posts' table. Is that too much? question, No 14 columns is definitely not too many columns for one table. I have a database with 55+ tables one table having 275 columns one having 158 columns and many other large tables. The trick here is to never run SELECT * FROM posts and only select the columns that you need and using where clauses with good indexing.

Upvotes: 2

Related Questions