I dont see a problem as long as you subtract from the amount owed on your invoice table, each time they pay. Each invoice should have it own number that get inserted into the commisions table. Once the commission is paid, you will need to also set that to paid, so you do not overpay commissions.
Douglas Brown Email: [EMAIL PROTECTED] ----- Original Message ----- From: "Tony Carcieri" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, July 22, 2002 11:42 AM Subject: RE: tracking payments > Douglas, > > Yea something like that. I originally thought about normalizing it and then > thought better of it. But now, it seems like it makes more sense to do it > this way. The only forseeable problem is that this could be a many to one > relationship. What I mean is this: > > Project Cost: 10,000 > Project Name: Test > Invoice Date: 1/1/2002 > Invoice Amount: 1000 (commissions go to sales reps/mgr for % on 1000) > > Invoice Date: 2/4/2002 > Invoice Amt: 3000 (commissions go to sales reps/mgr for % on 3000) > > so on and so forth till balance is 0 > > Is that possible to do? > > Thanks, > T > > -----Original Message----- > From: Douglas Brown [mailto:[EMAIL PROTECTED]] > Sent: Monday, July 22, 2002 2:17 PM > To: CF-Talk > Subject: Re: tracking payments > > > hmmm I would say more like... > > [invoice] > id > sales_person_id > Total Cost > Invoice Date > Invoice Amount > Balance > > [commissions] > id > invoice_id > sales_person_id > commission_amount > > then do a SP to calculate the difference in the invoice amount and amount > paid. Once this is done, then you can calculate the pay % for each sales > person and insert it into the commissions table. > > > > Douglas Brown > Email: [EMAIL PROTECTED] > ----- Original Message ----- > From: "Tony Carcieri" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Monday, July 22, 2002 10:57 AM > Subject: tracking payments > > > > Hi all, > > > > It's Monday, caffeine intake is low and I am having a brainfreeze. > > > > I need to keep track of payments made by the client (amount and date), as > > well as the commissions paid to the sales reps. The reps get paid each > time > > a client makes a payment; ie if the client pays 1000 of his 2000 balance > the > > rep gets a percent of the 1000 not the 2000. > > > > That being said, I am on a SQL 2K DB. I thought about having a couple of > > columns like: > > Total Cost > > Invoice Date > > Invoice Amount > > Balance > > Commissions > > > > However, I don't want to overwrite the existing data (as this is going to > be > > available for tracking and allowing the reps to print out an intemized > > view). > > > > I am having trouble of thinking about the best way to do this. I don't > want > > to put in a ton of colums (in case they are not used but likewist don't > want > > to put too few). > > > > Anyone got any ideas? > > > > Thanks! > > T > > > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
