Implementation of 'rules' and dependencies

[Matt Malyschko]

I am building an interactive quoting application for my client - they are in the business of roofing.

There are many intracacies to how their inventory items relate to eachother.

For example, 1 type of guttering requires a bracket every 700mm. Not all brackets are suitable for all types of gutters. So in the inventory table, I have created a portal titled "Suitable Brackets" but this only displays if the item type = Gutter.

This seems fine for this level of complexity..

When we look at a more complex situation - if the bracket type is not "External Bracket" then a clip is also required with the bracket. What type of clip? Well that depends on the type of Fascia we select.

Every item generally requires some other kind of item with it. For instance, external brackets require 2 fixings per bracket, a corner piece might require some fixings and some sealant. A clip might require 2 fixings as well, so if we choose a bracket that is not External then it needs to add the bracket, any fixings for that bracket, any sealant, or drill bits, etc. and then given it needs a clip as well, pending what clip we select will add the clip, fixings, etc. as well.

So there are often multi-way rules that affect what can be used with what, and I am struggling to get my head around how to handle it. If everything was always dependant and always needed to be added I could make that, but creating the structure so that all these multi-aspect rules can be entered by the end-user is proving difficult.

Any tips, advice or example files would be greatly appreciated.

Thanks,

Matt

[David Jondreau]

Sounds like you need a few moretables.

 

I'd say you need a table of "Inventory" which has one record for each part. A record for each type of gutter, clip, fascia, etc.

 

And you need a table for "Bill Codes" which has a record for all the pieces that would ship together to a client.

 

Then you need a table for "Assemblies". That's a join table where each record is a combination of a Bill Code ID and an Inventory ID. You'd also probably want a Quantity.

 

Some of how you set this up is what the purpose is and what your business logic is. For example, do you have set lengths of gutter or is everything custom cut out of a 12 foot length? Can someone order 5 1/2 feet when you only have 5 ft or 6 ft lengths? That kind of stuff.

 

Essentially though, when someone chooses a "Bill Code", run a script that looks grabs the related Assembly records and makes an Quote Item for each Assembly record (you can even multiply the quantity of the Bill Code by a Quantity field in Assemblies).

 

Bill Codes will be 6 ft Aluminum Gutter for so-and-so fascia. You'll need to "pre-build" all of your Assemblies, which will take some data entry.

 

Don't try to have more than one level of Assemblies, it'll be too complicated.

 

If it can be modeled in someone's head, it can be modeled in a database.

[Matt Malyschko]

Hi David and thanks for your reply.

I currently have everything listed in the Inventory table.

The Bill Codes does make sense, but the problem being that the item is not always shipped with the same parts, because compatability is dependant upon other parts selected.

Lengths will be varied (in mm). That is the point of this, to create the Lengths in a very straight forward manner that when somebody who knows nothing about the pre-requisites for each item can produce lengths that have no items incompatible.

For example - each length will have one corner on either end (not all corners are compatible with all gutters).

Each length get's assigned a "Profile" which is a pre-defined setup for gutter type, brackets, etc. so this is where all the rules get applied.

So in the profile set up, we select Gutter type = Quad. In the suitable brackets section we have added Over Strap, Concealed Bracket and External Bracket.

We will select Over Strap, at an interval of 1 bracket / 700mm of guttering. Because we did not select External Bracket we also require 1 clip with each bracket. The type of clip available to use (in profile setup) will be dependant upon which fascia type gets chosen for the profile. If timber we use one type of clip, if steel we use another. This is the part I do not understand how to implement, because I am struggling to generalize the rules so that they can be applied to any item for any combination (not hard-wired in).

My current setup uses a "Requirements" table, which is a child table of Inventory. The Requirements have Qty, Interval and Category. Say we are on the External Bracket inventory item. Under requirements, we have Qty = 2, Interval = 1, Category = Fixing. This means, External Brackets require 2 fixings on each Bracket. For that requirement, we then add some "Options" which are Screw, Rivets, Clouts.

That makes sense, but now when we want the fixing type to be dependant upon also the selected Fascia of the Profile... how does that work? So we have these 3 options, but if we select Timber Fascia, it defaults to Clouts, Steel Fascia still gives us a choice between Screws and Rivets.

Cheers!

Edit: could it be worthwhile setting up relationships similar to the Suitable Brackets that I have done on the inventory already? For example, on the Clout inventory, Suitable Fascia = Timber, while on Screws/Rivets it has the steel fascia, or more if there are other types added later. The reason I think I am against this is I want it to be more general than that.

Maybe two different sections- Requirements (as discussed above) and Compatibility where compatibility has the item type, for example fascia, and then as an option under compatability we have the timber/steel fascia.

[David Jondreau]

Well, it sounds like you're already worked on some options...

 

I've got to point out two statements:

"when somebody who knows nothing about the pre-requisites for each item can produce lengths that have no items incompatible."

and

"I am struggling to generalize the rules so that they can be applied to any item for any combination (not hard-wired in)."

 

I'm not sure you can do both of those. You may be able to build some dependency tree of possible configurations, but unless you've got thousands of configurations (and maybe not even then) the simplest solution is to simply build all the possibilities into the system.

[Matt Malyschko]

"when somebody who knows nothing about the pre-requisites for each item can produce lengths that have no items incompatible."

 

I guess this point is more referring to the actual creation of the lengths. If all the rules are set up through inventory / profiles (Bill Codes as you called them) then the end-user building the quote will just be applying what is available in each section, so the admin's will be responsible for setting up all the dependencies in the inventory (they know what is compatible and what is not)