A Data Modeling Challenge

Does anyone have any advice on this challenging data model? I need to create a database optimized for searches that will deal with some complex if-then scenarios. For example, let's assume that I have four entities--programs, region, pricing, and options (which can be multiple entities). Each program can be joined to one or more regions; each combination of program and region has one pricing matrix, but the selected matrix will depend on the options selected. So far, so good. The options can be combined to adjust or exclude pricing. For example, Program A Region A has option X + Y then use pricing 1. So far, so good. It gets a little more difficult when we have to accomodate the following: if Program A, RegionA, option X, option Y, not option Z, then use pricing matrix one, but add .25 to it. And to go a step further, we need to accomodate the following: if Program A, Region A, and 50 <X >75 and option Y is not chosen, then add .50 to pricing matrix 2. Again, this could be accomplished with procedures. The problem is that we need to provide users with the ability to querry all possible combinations. The customer will fill out a form to run the query and return the best possible prices. Should this be implemented in a binary, or is there a creative way to interpret the rules and weed through all possible combinations at run time? (I should note that there are many more entities; the scenario above is only a sample.) We considered interpreting the rules as the change (rules and pricing can change daily) and explode the data set into all possible combinations, but this could result in 100+ million rows, making the querry too inefficient.

Besides the challenge of interpreting rules at run time, there is the issue related to the maintenance (add/change/delete) and storage of your rules. You indicate that they could change daily which means that Users must have a mechanism by which to examine existing rules, and modify them as they please. Have you determined how this maintenance is to be implemented? Or is it part of the overall problem? From where I'm sitting (quite a distance, physically and logically!), the key for both issues seems to be the rule table. I could imagine the following scenario 1. Independant tables for each of program, region, pricing, and option 2. A Program-Region-Rule table which defines the pricing attributes (price matrix, loading) for a program-region-options combination 3. Program-Region-Rule table's primary key : program-region-rule#. 4. sequence# in the Program-Region-Rule table would define the order in which rules for a program-region would be interpreted. 5. The pricing attributes (pricing matrix, price loading) of the first rule which matches the customer's conditions would determine the price matrix to be used. Otherwise, the default rule would be used. 6. If rules live in isolation - i.e. independant of program-region, they would reside in an independant table with rule# as the primary key. 7. If rules are dependant on Program-Region (which is the impression I get), they will reside (or have a pointer) in the Program-Region-Rule table. 8. How will be the rule attribute actually be stored? Crux of the problem, really. It has to accomodate run-time interpretation as well as ease of maintenance. Could be (a) a blob column (flexible, but requiring a potentially complex interpretation program) or (b) a set of rows in a child table (less flexible, but easier to program). Just my 0.02 Rudy Todd Boewe wrote: > Does anyone have any advice on this challenging data model? > > I need to create a database optimized for searches that will deal with some > complex if-then scenarios. For example, let's assume that I have four > entities--programs, region, pricing, and options (which can be multiple > entities). Each program can be joined to one or more regions; each > combination of program and region has one pricing matrix, but the selected > matrix will depend on the options selected. So far, so good. > > The options can be combined to adjust or exclude pricing. For example, > Program A Region A has option X + Y then use pricing 1. So far, so good. > It gets a little more difficult when we have to accomodate the following: if > Program A, RegionA, option X, option Y, not option Z, then use pricing > matrix one, but add .25 to it. And to go a step further, we need to > accomodate the following: if Program A, Region A, and 50 <X >75 and option > Y is not chosen, then add .50 to pricing matrix 2. Again, this could be > accomplished with procedures. > > The problem is that we need to provide users with the ability to querry all > possible combinations. The customer will fill out a form to run the query > and return the best possible prices. Should this be implemented in a > binary, or is there a creative way to interpret the rules and weed through > all possible combinations at run time? (I should note that there are many > more entities; the scenario above is only a sample.) We considered > interpreting the rules as the change (rules and pricing can change daily) > and explode the data set into all possible combinations, but this could > result in 100+ million rows, making the querry too inefficient.

In article <s3meonlsrrp15@corp.supernews.com>, "Todd Boewe" <tboewe@lioninc.com> wrote: I'd encapsulate the rules into a source function and call that at the application level. This does not seem to be a database model related rule set. Now once you have this function, if you later upgrade to IDS.2000, you could create a UDF to implement the function into the server. For now keep it simple. Art S. Kagel > Does anyone have any advice on this challenging data model? > > I need to create a database optimized for searches that will deal with some > complex if-then scenarios. For example, let's assume that I have four > entities--programs, region, pricing, and options (which can be multiple > entities). Each program can be joined to one or more regions; each > combination of program and region has one pricing matrix, but the selected > matrix will depend on the options selected. So far, so good. > > The options can be combined to adjust or exclude pricing. For example, > Program A Region A has option X + Y then use pricing 1. So far, so good. > It gets a little more difficult when we have to accomodate the following: if > Program A, RegionA, option X, option Y, not option Z, then use pricing > matrix one, but add .25 to it. And to go a step further, we need to > accomodate the following: if Program A, Region A, and 50 <X >75 and option > Y is not chosen, then add .50 to pricing matrix 2. Again, this could be > accomplished with procedures. > > The problem is that we need to provide users with the ability to querry all > possible combinations. The customer will fill out a form to run the query > and return the best possible prices. Should this be implemented in a > binary, or is there a creative way to interpret the rules and weed through > all possible combinations at run time? (I should note that there are many > more entities; the scenario above is only a sample.) We considered > interpreting the rules as the change (rules and pricing can change daily) > and explode the data set into all possible combinations, but this could > result in 100+ million rows, making the querry too inefficient. > > Sent via Deja.com http://www.deja.com/ Before you buy.