Page 1 of 1

Debating Method To Track Member Fees - Over Thinking It

Posted: Wed Sep 30, 2009 12:57 pm
by jbh
Hey,

Members of a website I am developing use it to sell their product via paypal. It tracks the sales, handles the transaction/etc. After each sale, a fee is assessed based on the product price When they log into their admin panel, they view their profits, fees due, etc. I currently track fee totals by month as well as paid fees by month.

I was wondering if I should use an 'upgrade' to this system where, in case they want to pay more than once, I simply have a table called 'all_fees' and it's tied to their user_id. Should they make 3 payments per month or 5 or whatever, based on the fluctuating fees that amass from their sales each day, this table would allow me to delete from it anytime fees are paid.

This way, the fee total is a 'live snapshot of fees' and not confused with monthly fees/etc . In short, is it best to just host a table for fees and delete all references to it once payment is made and not try to track 'per month' as I was originally doing ?

Thanks for your time.

PS In case you are wondering why I initially tracked per month, the original idea was that they could pay once per month. While this system works now, I wanted to see if it was more efficient and logical to not limit the # of payments/etc.

Re: Debating Method To Track Member Fees - Over Thinking It

Posted: Wed Sep 30, 2009 1:39 pm
by josh
You should store the monthly cost with the customer, and every month an invoice should be created. Invoices:Payments should not be restricted to 1:1

Re: Debating Method To Track Member Fees - Over Thinking It

Posted: Wed Sep 30, 2009 1:51 pm
by jbh
If payments are not restricted 1:1, is having the separate table of fees a good idea then? This way
there is a way for me to calculate $0 due if they pay once, twice/etc and wipe out their fees
while still allowing others the option to pay once per month.

I can also update the monthly fees due by seeing what is in the 'fee tracking' table. Once payment is made, all references
to that table disappear. It basically gives me some flexibility.

Curious if you agree. TY

Re: Debating Method To Track Member Fees - Over Thinking It

Posted: Thu Oct 01, 2009 2:34 am
by josh
Hey, in your customers table you would have

cust_id
cust_name
cust_monthly_invoice_amnt
customer_balance

then youd have a cron script or something to that effect that generates invoices in the invoice table

inv_id
cust_id
inv_amount
inv_date_issue

Then when a user made a payment you'd insert into the payment table

pay_id
cust_id
payment_amount
payment_date


So for instance of Joe Blow was charged at a rate of $10 per month, each month of your "billing cycle" you would create a new row in the invoice table for $10. Let's say Joe didn't pay for 3 months he'd have 3 invoices... every night or something like that you'd want to total up these outstanding debts and write them to Joe's customer record to update his outstanding balance, this could be done via triggers, cron-scripts, or simply "derived" at execution time ( not stored ). When Joe logs in he needs to see a total balance of $30 tho and 3 invoices. But there's a lot of complexity if you wanted to go above and beyond, like double entry accounting, linking payments to the invoices, etc.. etc..

Probably a more advanced system would link payments to invoices so you can tell which invoices were past due, you then have to figure out in the above scenario that when Joe pays $30 it pays off invoice ids 1, 2 and 3 but not 4 for instance ( you can see how the complex programming can come sneaking up behind you ). Hint: you'd need an association table. Best to keep it as simple as you can at first though.