Calling & Storing PHP Values

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

You should not ‘store’ it anywhere. Calculated fields must remian that and not written to storage. Just run a new query and return the result through php.


select sum(amount) from donations

You may want to revisit your table scheme, it MAY be correct but all the ID columns are a bit unclear just by reading them.

Ok cool, thanks… a user_id can be a donator OR they can receive donations for a marathon

So essentially:

MARATHON_PROFILE

  • MARATHON_id
  • user_id
  • total_amount
  • 10 more columns

DONATIONS

  • DONATION_id
  • user_id
  • marathon_id
  • amount
  • 5 more columns

You’ll need MARATHON_ID stored on the donation table so that you can make a relationship between any dontation and the marathon it is tied to in the event that the same user id creates multiple marathons.