I am looking for some help in a database Iím designing for SQL Server 2000. It is a straight forward payment application but the part Iím wrestling with is that is needs to user customizable so the end users be establish new math rules via the application on calculating payments. The math will be simple: (+, - , *, /) but different vendors may have multiple calculations performed in determining the final payment. I would like to have the calculations performed on the database side instead of in a component. I think they will be faster done in the database, correct me if Iím wrong. Abstractly speaking, Iíll start with a VendorID and a base amount, apply calculations to that (could be 1, could 5 five) and have an end result. Between that and how best to handle the users adding their own calculations is what Iím struggling with. Could anyone point me in the right direction?
The speed of the calculation will depend on the hardware and resources available. If your server is old or you have a lot of clients attached you may find that client side processing is quicker as most desktops are not normally pushed by the average user.
If your custom building calculations for the customer then you could use stored procedures for server side processing. You could let your client software build stored procedures using a dynamic script but this does mean giving the client software wrights to create and drop procedures!
I guess the real question is how many rows of data do you expect to be in the calculation ??