Couldn't this ba a 3-table setup as follows: [Client] ClientNum BalanceDue .. other fields go here ...
[Transactions] ItemID ClientNum ItemDate ItemAmt ItemType (Inv or Pmt or Credit) [Commissions] CommID PmtID ClientNum CommAmt The main client table would hold the current balance due in a dedicated field, and that balance would be auto-updated when a pmt item was logged in. Same deal if an inv item was generated. You could calc the BalanceDue on the fly without using a db field, but you'd need horsepower to traverse each of the two tables on every client file page view, assuming balance is displayed on those views. Putting inv/pmt items into a single table makes reporting account histories much easier. Commissions are a child of Transactions, and a commission record is generated and permanently stored at time of each payment. ClientNum isn't strictly necessary but makes generating reports a little easier to code. --------------------------------------- Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc., www.mysecretbase.com --------------------------------------- ---------- Original Message ---------------------------------- from: "Douglas Brown" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] date: Mon, 22 Jul 2002 11:17:03 -0700 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 > > ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
