Hi there. As you will see from my questions, I am an SQL newb. I
dabble but never get to spend enough time to get proficient so base any
feeedback on that basis please. This is all theoretical information at
this point so I am also going to post this in a MySQL related group. I
will create some designs and post back to the group if I get any
feedback I can use.
Problem:
I would like to be able to keep a running percentage total in a field
associated with my users. In order to calculate the totals, I will
parsing a text file with entries from my users in it. The parser (AWK
etc) will search the file for specific text, compare it to information
in another file and output some entries into a csv file which can
subsequently be imported into the database.
The users make posts that are considered good and bad and the rating
percentage must be based on that. For example, if a user makes 10
posts in a day, and 4 of them are considered 'bad' by my criterion, the
rating should reflect a score of 60% for that day.
However, the rating is an ongoing value that will be adjusted daily and
I must maintain a running total against all previous posts. So, lets
say on day two the same user posts 10 more times and 3 are 'bad', I
must adjust his score to reflect a total percentage rating which would
then be 20 posts with 7 being bad for an overall rating of 65% etc.
My question is, how should I go about recording and calculating all
this information?
Here are my thoughts. I have a users table with a field called
something like 'Rating' which stores the overall value (65% etc). This
value would have to be calculated from fields in another table like
'Posts' which records each post in 'Good' and "Bad' fields that
increment. The Good and Bad fields would be incremented (populated)
from the text that gets imported etc.
Looking for thoughts from experienced db designers please. Thanks a
lot in advance for any responses.