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.
Commissions Paid & Discounts in Payment DB
Moderator: General Moderators
In any ordering or financial system there should be an easy way of backtracking everything that was going on, so anything related to a transaction should be recorded, perhaps not in the transaction table, could be a "order" or "Journal" table of some kind..
What happens if a client or org changes their name/phone or something here? The original data must be stored somewhere, either by active/inactive records (so the records are write-once-never-change) or by storing the info associated with the order/transaction(s).. Basically anything that can chage later on must be stored forever with a transaction relation..
Also, any info that can change should have from and to dates, as well as original creation date and perhaps self joins (derived from record) if using such an approach, which I would say is common and often a better normalization than storing all the data with each order/transaction..
What happens if a client or org changes their name/phone or something here? The original data must be stored somewhere, either by active/inactive records (so the records are write-once-never-change) or by storing the info associated with the order/transaction(s).. Basically anything that can chage later on must be stored forever with a transaction relation..
Also, any info that can change should have from and to dates, as well as original creation date and perhaps self joins (derived from record) if using such an approach, which I would say is common and often a better normalization than storing all the data with each order/transaction..