Databasing Questions
airbornflght
Houston, TX Icrontian
I figure over the next few days I'll be asking a lot of questions related to best practices with databases so I'd just make one umbrella thread. Anyway, I have one question tonight that I asked myself while laying out the increasingly complex database.
I decided that I'd like to add a rating feature where a user can make an up or down vote. and the objects with the best ratings will get the higher spots (think digg). The math for the rating will be "positiveVotes/totalVotes" and that will generate a percentage.
So I came up with two ways that it would be possible to store these votes.
The first method was to create a 'votes' field within the objects table. So that there was one vote field per object, and the votes would be stored as a comma delimited value. The data would look like "x,y" where x is a positive vote and y is a negative vote and I could figure all the math from there and when a vote was cast it would read those values, and update them.
The second method was to create a separate table called 'votes' which would have three fields: "object, vote, voterID" where object is what's being voted on, the vote would have a T/F value, and voterID would be the user who voted.
The second solution offers more flexibility that the first solution doesn't necessarily offer. But the first one seems more efficient. I'm wanting to gather some feedback from some more seasoned web devs than myself on what is this best practice and what would you personally go with. As identifying the voter is not necessary but would be nice in case the situation would arise to need to identify votes.
I decided that I'd like to add a rating feature where a user can make an up or down vote. and the objects with the best ratings will get the higher spots (think digg). The math for the rating will be "positiveVotes/totalVotes" and that will generate a percentage.
So I came up with two ways that it would be possible to store these votes.
The first method was to create a 'votes' field within the objects table. So that there was one vote field per object, and the votes would be stored as a comma delimited value. The data would look like "x,y" where x is a positive vote and y is a negative vote and I could figure all the math from there and when a vote was cast it would read those values, and update them.
The second method was to create a separate table called 'votes' which would have three fields: "object, vote, voterID" where object is what's being voted on, the vote would have a T/F value, and voterID would be the user who voted.
The second solution offers more flexibility that the first solution doesn't necessarily offer. But the first one seems more efficient. I'm wanting to gather some feedback from some more seasoned web devs than myself on what is this best practice and what would you personally go with. As identifying the voter is not necessary but would be nice in case the situation would arise to need to identify votes.
0
Comments
However with doing a simple percentage only your system may be schewed. For example. A comment with 1 possitive vote would have a 100% ranking if you go through a simple percentage system. Where as a comment with 990 out of 1000 votes would rank lower.
You'll need to also some how offset the number of votes so that a comment with more votes even if it has a lower percentage could be worth more then one with less votes and a higher percentage.
Good point. Perhaps a weighting system where the more total votes the heavier weight of the percentage?