The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database-driven calculation logic

We have a client that needs to perform some fairly sophisticated calculations with records in a database. (Finding the proper payment for a medical claim based on the insurance contract.) The problem is that the exact calculation is going to widely vary depending on the details of the contract.

v1.0 of the application was pretty messy: there were some basic database lookups for amounts associated with certain types of procedures, but all of the logic, sequence, and exceptions were in the code itself.

For v2.0, they want us to totally redesign it so that they can be the ones to enter/maintain the contracts themselves, and the calculations will just work correctly.

I really only have a vague idea of how I might accomplish this--maybe tables that define different types of calculations that can occur, and provides the ability for the user to define the parameters and the priority of the types.

Is there a name or pattern for this type of behavior? Are there standards or models for doing this kind of thing?
BradC Send private email
Tuesday, December 14, 2004
Sticking my neck out here, because I don't claim to be good with patterns, but this sounds like a Factory.
Anony Coward
Tuesday, December 14, 2004
I don't know anything about the patterns that might be associated with this sort of thing, but it strikes me that the easiest way to do this would be to incorporate a scripting component or just a lighter-weight expression evaluator component into your app. 

That way you could basically store the actual code (of the formula) as a string in the db and easily evaluate it at runtime.  And it should be relatively easy for user to modify: they just change the formula stored in the db.

Here are links to a couple of expression evaluators (that turned up in a quick web search) that may give you a better idea what I'm talking about:
Herbert Sitz Send private email
Tuesday, December 14, 2004
In general, this is a Strategy pattern.  In specific, its often called a rules engine, and there are various free and commercial ones on the market. 

My advice would be to keep it fairly simple to start: define "types" of contracts with parameterized calculation code.  So each type maps to a piece of code that implements the set of rules.  This is done easily in Java and C# with reflection.  In other languages it might be a custom script that is loaded for the type.

So when they add a new one, they can pick from a pre-defined list of contract types, each of which has a set of these parameterized rules.  You can reuse some of rules between contract types, presumably.  Each type would know what parameters it needs, and your users could enter these.

Good luck.  But set some boundaries early - this sounds like a hard problem if there aren't any.
Tuesday, December 14, 2004
>>Good luck.  But set some boundaries early - this
>>sounds like a hard problem if there aren't any.

Ya, that's what happened the first time:
"Oh, its really easy--just look up the charges from this table."


By the end we had crazy unique exceptions for specific COMBINATIONS of charges:
"If you have an ER with an Observation, pay 100% of the ER and $67 per hour of observation, but only up to 23 hours. If there are any high-cost drug charges with them, pay 65%, unless they exceed $2000. Oh, and if there are any diagnosis codes of 913.11 through 913.75, then wipe out everything we just did above, and pay $8500, unless that exceeds the total billed charges, but make sure to subtract the Implant charges before you limit to billed charges, then add back 50% of the implants."

Made that one up, but its not far off. Oh, and that's one of about 12 funky clauses.... of just one of about 30 different insurance plans.... of just one of two hospitals (soon to be three).

And it needs to be flexible enough for THEM to enter new contracts.

The good news about our development environment is that is already includes scripting support. (That's actually what I'll be developing in). But I can't necessarily expect the user to be able to modify the scripts themselves.

I'll take a look at "rules engines". Of course, I really have to CREATE on, not just USE one.
BradC Send private email
Tuesday, December 14, 2004
Brad, last time I worked with billing logic like you are describing, I looked into some rule engines. The marketing dept had this LSD scheme to add about 10x the number of customers and about 50 new billing schemes.
Chapter 8 shows something very similar to CLIPS.
I have an older version of this book. My version had a copy of CLIPS on the floppy.

At that time, there was a FL based company making an activeX control which implemented something very close. However, I was sidetracked by the mad rush to migrate the DBs from Access97 to SQLServer7. Last time I looked, that company vanished.

You are also going to need a good statemachine.
is pretty good. There are .NET ports available on the web.
Peter Send private email
Tuesday, December 14, 2004
a more specific search term might be 'business rules engine'

just one of the many links that comes up in google

i think the hardest part about building a rules engine is building the UI for non-programmers to add/update the business rules.
Tuesday, December 14, 2004
One thing is certain: if you build a rules engine that perfectly anticipates and handles billing parameters for every medical procedure ever conceived, your first major enhancement request will be to handle some new tax or union rule or whatever that's calculated on some basis you never foresaw.
NetFreak Send private email
Wednesday, December 15, 2004
From the links I have at the office:

I missed the "user changes rules" part, so like the other posters said, you might want to add some sort of visual testing regime, like NUnit or JUnit. Let them make some "golden claims" with a matrix:
Under plan 34, this claim pays $78.00
Under plan 129, this claim pays $8102.22
Under plan 4333, this claim is rejected totally.
Under plans 34, 221 and 127, claims with diagnostic code 402X must be routed to department 666 for manual review.

Let them be responsible for validation of rules and payments.

If they are doing the entry of the rules, there *may* be a time when someone royally screws up the entry in the system and over/underpays by a 6 to 9 digit amount. Expect ravenous lawyers with lasers in their foreheads when that happens. So you might want to keep logs in the database of when a rule was added or changed, and who did the addition or change. If you are really slick, keep a copy of the old rule in a log, so you can figure out who done what and when.

The last time I worked with lots of billing schemes, there were some wildly different ones, but mostly one could sit down with the contracts the sales department came up with, and translate them into code pretty quickly. The marketing dept had gone into a crack smoking binge and was starting to come up with schemes that were bordering on impossible to figure out (put 5 people in a room with the contract, and you will get 6 wildly different translations). I think they saw their cell phone bills and credit card bills and had $$$ dancing in their dreams.

Interesting project. Will be fun, and hard. Has lots of interesting possibilities. I'm jealous.
Wednesday, December 15, 2004
Ged Byrne Send private email
Wednesday, December 15, 2004
Lots of great ideas, guys, thanks.

Good news is, I don't expect laser-headed lawyers to descend if/when something goes wrong :). Only 6 months ago, my client had been MANUALLY calculating all this stuff. So they should know pretty much at a glance if something doesn't pass the smell test. My application will be used as an automation tool, really, but the auditors are still responsible for verifying the results.

We also have the billable amounts calculated by two other parties (the hospital and the insurance company) to compare.

I like the testing idea: have a standard battery of claims, and have them run the claims against a contract they have entered, letting them examine the results and see if they calculate correctly.

I'm still a little fuzzy on the method for users to enter rules--when I sketch it out I end up with a branching tree of choices that would be hard to illustrate just using a list of types. I wonder if there is a way to let the user build a visual flow chart of events and choices. hmm...
BradC Send private email
Wednesday, December 15, 2004
For building rules, consider an interface like that used for building filters in Thunderbird, Outlook, or Outlook express.  That's the closest thing I can think of in well known commercail *cough* software.
Joel Coehoorn
Wednesday, December 15, 2004
Joel- I really like that idea. It allows for quite a bit of complexity, but is a fairly easy way to allow the user to apply rules.
BradC Send private email
Wednesday, December 15, 2004

Perhaps you should code it as a workflow applicaiton, which is the closes I know of to the flowchart idea.

What setup are you using Java, .Net, VB, Oracle, SQL Server???
Ged Byrne Send private email
Thursday, December 16, 2004
Well, the infrastructure is going to be built on a CRM application called SalesLogix. It has some critical functionality already built-in that I won't have to create from scratch: calendaring, security, appointments, notes/history, scripting, mail merge, Crystal reporting, etc.

SalesLogix is built on SQL server, and includes the ability to generate custom forms, vbscripts, incorporate external ActiveX controls, manage custom tables and data, launch external applications, and pretty much do whatever the heck I want.

There are certain elements of this process that will have to be coded in the native vbscript (ugh), but other portions I may develop in .NET and integrate.

The rules-building process is one aspect that not all users will need to manage. This could even be a completely external .Net app, as long as I can figure out the details of how the internal scripting component is going to "run" the rules and calculate the payment for each claim.

Ack, dirty trick. Upgrading FogBugz while I'm entering a comment. All fixed, I guess. So this is v4.0?
BradC Send private email
Thursday, December 16, 2004
I've built quite a few of these types of system over the years.

I second what GuyIncognito said:

>i think the hardest part about building a rules engine is >building the UI for non-programmers to add/update the >business rules.

Some of the things I have tried are:

flowchart type thing with the rules implemented in a similar way to MS Access expression evaluator. This was all done in VB and worked reasonably well.

As above, but rules flowchart implemented using Visio type library - works better than #1 for a lot less effort.

Current (back burner, fer interest project) version is .Net browser based UI with drilldown. Supports "rule group" objects - basically you can define a subset, then reuse it.

Each rule needs:
An identifier
A name
A description (for tooltip in flowchart)
A calculation - the bit that gets processed. Evaluates to true / false.
On true goto rule #identifier
On true action (another calculation to e.g. set var, get data etc)

On false goto rule #identifier
On false action (another calculation to e.g. set var, get data etc)

The rules engine:

You can get a rules processor commercially and then use its API. There are a few available. I never tried this though.

Version 1 - Rules compiler in VB. Compiled rules created using UI defined above (flowchart / expr analyser). [Actually the compiler was written by a colleague] This was all hard coded - every rule was written in the expression analyser in a subset of VB, then tokenised / parsed and compiled. The compiled rule was saved in the DB. Key learning: Don't do this! Use the VB scripting object to process rules.

Version 2 - Used VB scripting object. Much easier, but not good for high volume (transactions).

Version 3 - Rules defined in XML / XSLT. Used MSXML to parse and spit out results.

Version 4 - (current .Net) mixture of 2 and 3. There's probably a more elegant way to do this. Probably need to create rules language and processor.

Difficult bits:
UI - always a problem
Data reading / writing. Not to hard. You need a Data Adaptor. This is the part that will absolutely kill performance. Ideally you need stateless processing coupled with asynchronous read / write.

I'm sure I've some useful links somewhere. I'll dig them out.
Justin Send private email
Wednesday, December 22, 2004
Check out Autorete (since renamed to HaleyAuthority, I guess) at:

It's made for exactly this kind of problem and works very nicely.
hope this helps
Thursday, December 23, 2004

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz