How would you approach this problem?

NancyJ · 01-15-2013, 08:39 PM

eg. Boxes going for delivery. You want to put notes on the boxes depending on certain dynamic criteria.

examples of criteria

All Boxes going to Destination X
All Boxes going to Destination X from Departure Y, Departure Z or Departure A
All Boxes going to Destination X or Destination Y
Small Boxes going to Destination X or Destination Y from Departure A, Departure B or Departure C
(in total there are about 5 different criteria so far that can apply)

So I need to build a database and interface that will allow someone to create these 'rules' where each rule can have multiple different criteria and each criteria can have multiple values. And I need to do it in a way that a normal person could use.

When the rules are in place, when a box comes in it needs to find all the rules that apply to it.

So my big problem areas are the interface design and structuring the database so that the information can be easily stored and queried.

So far what I've come up with is

Code:

Rules
    id
    message

Criteria
    id
    rules_id
    type

Critieria_Values
      id
      criteria
      value

but I don't think that is going to be easy to search.

Fou-Lu · 01-16-2013, 04:41 PM

Can you describe more of the rules and how they'll need to be used in the code? Code wise I'm leaning towards a decorated Rule object as being the easiest approach (rules composed of rules which therefore provide infinite depth of rules), but how it applies may not be as simple. An intercepting pattern may be useful for this if you were to say, filter it. You create the filters based on the criteria, and then you send the object down the "pipe" to see if it makes it through. If it does, it would then pass all of the filters required. If it throws an exception, then it doesn't make it all the way (and infers that it would therefore not match a criteria). This also doesn't actually play that well into the OR logic and follows an AND logic instead. The tricky part here then comes to simply the evaluation of the rule. Once again, this use would be dictated by what you are actually needing to do.
The way I'd probably see this is: Use an intercepting approach if you need to "test" something as valid. IE: I know my rules and construct them, then provide it with data to see if it passes.
If I need to simply see my rules, I'd use a decorator to compose them.
Note that you can do these in procedural as well, but I'd suggest OOP since it would be easier to write either of the patterns.

Structure wise it looks okay. The rule is simply the message, and assuming the 'type' is that of criteria_values, that would flatten the rules to the criteria, giving you a many to many relationship of the rule to the criteria_values. That looks good. Searching should be easy. You can join to get all the criteria that a rule uses, or you can find all the rules that a particular criteria applies to. Three tables is correct for writing a single many to many.
If you can clarify a bit on exactly what you are needing to do with the rules (how they are applying), than that would help. I'm a bit on the fence on an approach I'd recommend simply because I'm not sure how you intend to use it.

Edit:
In hindsight, I'm not sure I interpreted these structures correctly. I think the structure you have here indicates that criteria_values is aggregated by the criteria instead of the other way around. I'd see this more as:

Code:

+----------+ +--------------+ +------------+ | Rule | | CriteriaRule | | Criteria | +----------+ +--------------+ +------------+ | id [PK] |>o---+<| r_id [PK] |>+----o<| id [PK] | | msg | | c_id [PK] | | value ? | +----------+ +--------------+ +------------+

Where I'm not sure what belongs in the Rule and Criteria. But that would be how you flatten the many to many of the rule to criteria.