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 > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm 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
