Jump to content

Archived

This topic is now archived and is closed to further replies.

justravis

Commissions Paid & Discounts in Payment DB

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.