justravis Posted July 18, 2003 Share Posted July 18, 2003 I\'m creating a financial db that includes the tracking of commissions paid out & discounts given. Referrals of clients will most likely be clients themselves. That is why the referrals table has 2 fields that are foreign keys from the org table. How would you track commissions paid? Just add a field to transaction table or create a new table? As far as discounts given, clients are given a percentage that will be discounted until thier total discount is met. Is my implementation adequete? organization a) id B) name c) web contact a) id B) org_id c) name d) phone e) email j) username k) password service a) id B) name org-serv a) id B) org_id c) serv_id d) frequency 0 (one-time) 1 (annually) 4 (quarterly) 12 (monthly) e) pres_cost f) beg_date g) end_date (stops billing, but yet records past subscriptions) Discount a) Id B) org_id c) percent d) cum_disc e) tot_disc transaction a) id B) o-s_id c) date d) cost e) discount f) amt_paid referral a) id B) referrer (org_id) c) client (org_id) d) comm_rate I APPRECIATE ANY ASSISTANCE. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 27, 2003 Share Posted July 27, 2003 A couple of questions spring to mind Discounts - why a separate table, or are there more than one record per org_id? If there are, perhaps they should be dated and have the discount amount, not calculated values ( you can calc as required) Not a good idea to store derived items anyway) Same would go for commissions. Does the commision rate vary for each referral? Is it dependent on some other data/key? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.