I have an education provider table: that lists among others item, the commission %, which is hard-coded ( written manually as per our agreement with them) every time we have a new education provider sign up with us, we create their record. We have this system working fine. But now there is changes in the commission % with one of the education providers. We can’t just simply change the commission percentage in the commission % field of the Education Provider table for that education provider record, because by doing this, it will wrongly calculate the commission for all previous clients related to that education provider based on the updated commission %.
What’s the solution for this? apart from make a new commission % table and link the new created commission % table with Education provider’s cCommission % field ?
The best way would be to create a new field in the same table and write the commission manually. I would actually suggest you make the new table with all the commissions and link it but for some reason you dont want to.