Database design to store and evaluate rules expression

Question

I have a scenario where I need to assign some Items to a Guest.

GuestTable has columns like
1. Guest ID
2. GuestType
3. Age
4. Height
5. Gender

ItemTable has columns like
1. ItemID
2. Item Name

Every item is sold to guest based on item's availability as well as attributes of guest i.e. Age Group or Height range etc.

eg. An item is available every Monday and Tuesday from 2-4 PM and 6-8 PM for Male Spa guest as well as Hotel Guest having height greater than 5" and less than 5'5".

Since number of filter conditions are large and configurable by end user. As well as system should be scalable enough to accommodate new filter criteria, How should I manage these rules. Is it OK to store these rules as expression in database tables? How to model the database tables for this scenario?

OR

Should I consider using some rule engine for it?

It sounds like you're creating your own expression builder, so you'll need to present the user with all possible columns and a list of operators (+, -, <, etc.). The I suspect you'll need to convert that into a valid WHERE condition, store it as text, and use dynamic SQL to generate the end query. Alternatively, you could create an SSRS report with lots of parameters to display a report of valid items. Only a comment because I'm hoping someone has a better suggestion. — Russell Fox, Commented May 5, 2013 at 20:34
Without a design for how you are going to apply the rules then how do you expect to get an answer for how to store the rules. Is a rule specific to an item? Really you have an item you would not sell to a guest over 5'5? How about inventory? Cannot sell an items you don't have in stock. So much is missing. — paparazzo, Commented May 5, 2013 at 20:36
To use a Rules engine is an architectural decision. You can't expect us to tell you the right answer. We simply don't know enough about your situation. An Engine might be appropriate, you certainly present something which might justify one. However, there is a whole bunch of expense - licensing, learning curve, etc - associated with an RE approach which we cannot evaluate for you. — APC, Commented May 6, 2013 at 7:04
Do you have a single database? Or is each customer going to have their own? If the latter is true, storing them in the DB will create many different permutations of the rules to support. — Ryan Gates, Commented Jun 11, 2013 at 18:12

Neville Kuyt · Accepted Answer · 2013-07-19 10:54:02Z

1

As APC writes in the comment, deciding on whether to use a business rules engine is not something you should trust to a bunch of strangers on the internet.

However...your scenario is relatively straightforward and constrained. It appears to be a single step, rather than a workflow - "if customer meets criteria x, offer product y", rather than "all customers matching criteria x must be approved by a manager". It appears to be constrained to just the database - no invoking web services to decide whether a product is suitable.

On that basis - no, don't go for a rules engine. They're big, complicated machines that only pay for themselves in far larger problems than the one you describe.

I'd suggest using a .Net "query by example" library to allow your users to create the filter conditions; there are several commercial options, such as EasyQuery.

edited Jul 19, 2013 at 10:54

answered Jul 9, 2013 at 12:14

Neville Kuyt

29.7k2 gold badges40 silver badges52 bronze badges

Thanks, "query by example" can you name a few?
– Vaibhav Jain
Commented Jul 18, 2013 at 17:22
I found only one - there used to be one from Telerik, and Crystal had something too, but can't find links...
– Neville Kuyt
Commented Jul 19, 2013 at 10:54

Add a comment |

Soham Dasgupta · Accepted Answer · 2013-07-09 11:54:25Z

1

You should think about using something like Flee and avoid using the database as a rules engine. Client side parsing will be much faster and will not put as much load on you database server. Just read in the values and then parse the expression using Flee. It a great evaluation engine. Try and let us know what you think.

answered Jul 9, 2013 at 11:54

Soham Dasgupta

5,24926 gold badges81 silver badges129 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Database design to store and evaluate rules expression

2 Answers 2

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related