Create a complex commission equation
-
I am developing a data base for an education center (sql 2000). The problem I have is how to create the staff payments because they are very variable:
The center provides around fifty different services for children and sometimes the children will pay in foreign currency, also they may pay per session or per hour depending on the service. There are about 70 staff members, each receives a different percentage from child payment and sometimes, a staff receives a supervision charge for other staff.
I designed the database as a main table for staff, table for services that includes the payment in local and foreign currency and table for children that includes the currency.
When I tried to develop a procedure to calculate the payments it looks like this:
Case when tblchildren.currency = '$' and tblstaff.staff = 20 and tblservice.serviceid = 1 then tblservices.childpayment * tblservices.exchangerate * datediff(mi, starttime, endtime)/60 * 0.7
Now this code does work, but as I started to add all options for different currencies as well as session or hourly payment and staff variables, I exceeded the code limit. Also, administring such a code is extremely a nightmare. Any help will be most appreciated
-
It is hard to give good advice without DDL, but here are my 2 cents:
a) Use ISO codes for currencies and don't hard code them. What is '$'? US dollars, canadian dollars, etc.
b) You seem to mix children, payments and currencies. Normalize your schema.
c) If you put business logic directly in the database instead of in the application, try creating layers. Not obvious with procedural code like T-SQL, but you can use views (or CTEs with SQL 2005
). The CASE statement your complain about deals with exchange rates, currency type (hard coded 
) , service type, time interval, etc. And I am not sure what that "* 0.7" is about (not some kind of tax I hope). If you normalize and design your layers correctly you should be able to get rid of CASE entirely.
Hope this helps
-
Thanks for the tips but how can I program different currencies e.g we use L.E (egyptian pounds) and the exchange rate is variable on monthly basis?
The 0.7 is the an example of staff percentage of children payment which is variable according to staff and service
I appreciate if you can explain further about normalizing data
-
Here is a good example explaining the database normalization process
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply