Hi, i am working on a site that allows members to make a small donations to a user's (friend) marathon profile page… i have a 'marathon_profile' table in my database and i have a 'donations' table in my database. MARATHON_PROFILE - id - user_id - total_amount + 10 more columns DONATIONS - id - user_id - marathon_id - amount + 5 more columns I need to display the total amount raised on the marathon profile and i am trying to decide on how i should store / record the total amount raised and how i should query the total amount in order to display it: 01 - which table should i store the total amount of money received in A - simply add / increment the $total_amount field in the 'marathon_profile' by the individual donation amount AND record each individual donation $amount in the 'donations' table B - JUST record the individual amount in the 'donations' table and forget the idea of having a $total_amount and incrementing it each time 02 - how should i call for the total amount so that i can display it on the marathon profile page A - just call for the $total_amount in the 'marathon_profile' table (this seems a lot less workload on the site) B - run a query which will add up add the $amounts WHERE marathon_id = the marathon page (this seems a lot more work load on the site) Thanks in advance for your help
In terms of load, keeping a running total is always better...I mean it's just a matter of setting up two functions or methods - "add/subtract amount" and "get total" then calling them when appropriate; in that way it's not really enough 'extra' work coding-wise for anyone to kvetch about doing... Which is not to say you won't have the dumbasses chime in any second here with 'just do a SUM in mySQL'. Simple fact is you're likely to have a lot more people looking than you are donating -- so to keep the server load under control, run that extra total when appropriate... then you aren't running through every blasted record in a large table just to get one value. Bottom line -- A and A. Anything less is just sloppy/lazy coding that results in slow loading pages and higher server load.
Thanks for your reply... i have posted this on a few forums and out of 8 responses you are the only one who chose A and A I was thinking it was a better idea to do it your way too...
I'm going to go against deathshadow and say store it in one location (so B and B). Storing it in two locations unnecessarily complicates things. It means whenever you mess with the donations in one location (i.e. deleting a donation, editing a donation, adding a donation, etc.) you have to similarly update it in the second location. It's fine if it's an isolated incident, but when you're working on larger projects, doing it all the time for a range of different use cases is ridiculous. If it's a question of performance, you could (and probably should) just cache the result to negate any performance issues.