Should I use scripting or some other means to perform complex formulas?

Question

Background

Imagine a purchasing system where people place orders. Now those orders can represent a large amount of cost to the company, so we try to calculate an estimated cost to have an idea of our expenses without having to wait for an invoice.

Formulas or something else?

One of these costs on the order is very complex. Each vendor has a different way of calculating how much they will charge and each has their own little "fees" to add to the price. The formula itself can change and I would like it to be user maintainable.

So, I thought, I could store a "formula" which is a C# script associated with each Vendor entity (I'm using NHibernate) and compile that script at runtime and it can calculate the cost. Each vendor can have their own formula and the formula can be very simple like "quantity * unit cost" or it can be very complex having all of the "fees" and what not.

But, maybe there are other ways of doing this? My fear is that this calculation is going to become very complex, not user maintainable, and inaccurate. So, it would be a lot of work without a lot of benefit.

Advice

So, is this insane? Is there a better way to do it? Does my scenario make sense?

Answer 1

Why using a database?

You can create a Vendor class with a UnitCost method/property and create subclasses for each formula.

Ok you can still store them in a database, but that has no real added value.

Answer 2

I might be thinking too simplistically here, but I'd try and approach it in the same way that I'd handle a rate card of some description- a matrix of values which are calculated based on cross-referenced parameters. Doing it this way means that you can break the calculation down to smaller, less error-prone atoms. If you then allow vendors to adjust these values, but not the operation performed on the value then the output is less likely to be wrong.

A bit like presenting somebody with a spreadsheet and telling them that if they alter A3, A8, B17 and C3, then D1 will be the cost. grin.

Hope that helps.

Gav