Storing Conditionality in Database

Need help Database
1

I’m working on a CPQ tool. My “creating bids” process involve three main tables: “Products,” “Measurements,” and “Requested Products/Services.”

In this setup, “Requested Products/Services” encompasses all the offerings customers can request. Products are associated with “Requested Products/Services,” and a contractor may have multiple products tied to a single “Requested Products/Services”.

Measurements contain everything our team measures relating to the requested product. Measurements are linked to both product units and conditionality. The conditionality includes fields like “Linked Measurement,” “Operator,” and “Value,” such as “Total roof size” “greater than” “1200 sq ft.”

I’m currently stumped on how to handle AND/OR logic for these measurements on a single product. For instance, how can I manage conditions like:
“Roof size” “greater than” “1200 sq ft” AND “Roof Pitch” “less than” " 40 degrees"
OR
“Current roof type” “equals” “metal”

Thanks in advanced for any insights you can offer on the topic.

2

It’s definitely not so simple, especially if you don’t have all the possibilities in advance. I’d try to break it into categories like, based on your example, size, pitch, material. Then the contractor enters pricing for their custom defined sizes (unless there are industry ranges) and an add-on for pitch (either per sq ft or $ of base quote) and the same for different materials.

The hardest part may be getting contractors to delineate such pricing. I’ve had a similar experience where I asked the user a whole bunch of questions (e.g., what about that same service but expedited or with marble) and then extracted such conditional rules and verified with the user.

Once you tease out the details, nesting all conditions under a service will allow you to compute a quote. once customer enters the specs, you’d find the range of the roof within that product and increase based on pitch and material…