Jump to content


Photo

Commissions Paid & Discounts in Payment DB


  • Please log in to reply
1 reply to this topic

#1 justravis

justravis
  • Members
  • PipPipPip
  • Advanced Member
  • 115 posts
  • LocationSan Diego, CA

Posted 18 July 2003 - 06:34 AM

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.
PHP version 5.2.6 | MySQL version 5.0.51a-community

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 27 July 2003 - 05:35 PM

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?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users