I have a database for one of my websites, a picture rating site. Anyways, right now there are quicte a few tables, and I was wondering how to give the server a break and was wondering if this was possible:
Basicly I have a members table, and a votes table. Members will rate other users pcitures on a scale of one to ten, then the votes will be inserted into the votes table. The only problem with this is that calcuating all the votes a user has can put a straing on the server. I was wondering if it would be possible to create a math column in the members table that would automaticly figure out the users average and having it stored in a field in the members table, so all I would have to do is query the members average located in the mebers table, rather than tallying all the votes in the votes table for each member.
Hope this makes sense, a tutorial or any suggestions would be great!
ThanksYou should be able to add 3 columns to your Members table to help with this: TotalVotes, TotalScore, and AverageScore. And then you can add a trigger to your Votes table which would update these columns in the Members table each time a Votes record is added/updated/deleted.
You can see what Books Online has to say aboutTriggers. And here's a little overview which gives a friendly overview of triggers:The Trouble with Triggers.
I might add that I am completely paranoid about this approach :-) I would want to periodically refresh these fields in the Members table (probably with a SQL job that runs nightly) to make sure the data stays in sync.
Terri
No comments:
Post a Comment