I suspect you're correct about how I'm going to end up with approaching this in this method.

Perhaps it'd make more sense if I explained the business process

The system is broken out into groups of customers - each customer can have an admin. Each admin can set up the system for just their particular group of participants, products, services etc. In my example I skipped the customer part of it as I can add that back in easily enough and wanted to keep it simple.

Each participant the admin enrolls needs to be assigned a particular tier, that tier dictates what services are applied to the participant - that participant then has data collected based on those services.

Tier 1 gets service 1 and service 3

Tier 2 gets service 1 and service 4

Tier 3 gets service 2 and service 5

Tier 4 gets service 4 only

Lets assume service 1 and 2 are very similar, as are 4 and 5. If we assigned Tier 2 to a Tier 3 participant by accident, it could be missed due to the similarity to the data required. (it could be that service 2 has 1 additional question thats vital for that particular tier, but not for the other - if we miss that question, the data is useless) So to eliminate that, I wanted to create a rules table to ensure that only the appropriate tiers are available to the admin who is enrolling the participant, depending on the participants information.

Now obviously, I wouldn't know what the business rules are for the customer's particular tiers - and I don't really want to know.. trying to maintain their business rules would be a nightmare.

So I wanted a simple way for the user to be able to implement their own rules.. so when they create the tier during setup, they'll have a list of fields that they can use in the rules, and they can supply values for them.

Field | Operand | Value

ie.

Age | > | 5

From that, the hope would be that I can take the participants information, read the rules and match my data to the rules to come up with the tier that is best suited to the participant.

What I had done previously is something like

Tier | MinAge | MaxAge | Hair Color | Eye Color etc.

But as I make more fields available for the rules, I end up with added overhead to altering the rule table (which had a ton of columns) and modifying the lookup SQL.

So the hope was to make it a little more hands off, and thats where I came up with the rules table I have now

field | operand | value

With the hope that they can just select the field name, select the operand (=, <, > etc.) and the value they want to resolve to.

But - nightmare!

As I stated early on.. I *can* get the correct tier with my model - a 2 query process, the first gets the rules and generates the SQL, and then that SQL to get the tier.. but its just clunky and can't possibly be as efficient as I'd like it to be - not a problem now, but in 2-3 yrs, that could be an issue.