Users need to create dynamic rules that apply to other users access to the group

Question

Okay so here is "in a nutshell" what I'm trying to accomplish...

Users of my application can go and create a new group. They can specify criteria about other users which they will allow/deny to determine who is allowed to join the group.

Example: Age: 12 - 16 yrs old Height: 5 - 6 feet

The data table that stores the rules would be like: Allow or Deny Flag (Y/N) AgeStart: 12 AgeEnd: 16 HeightStart: 5 HeightEnd: 6

We would then need to check if the user meets the criteria to determine if they are allowed to join the group.

Does this make sense? There can also be EXCEPTION rules which go against it... such as first "Allow: Y" might be Ages 12 to 30 but a second record might be added to Deny ages 25 to 28 so the user would have to meet both criteria to enter.

Any takers on this one?

Answer 1

Yes, you can store the rules in your database using whatever kind of DSL makes sense to you, but SQL is not a solution for interpreting or enforcing the rules.

It'll be far easier to fetch the rules field into your app and interpret it there. You'll have access to feature-rich parser libraries to interpret the rule, and easier ways to write OO code to apply it.

Since you only need to run the rule as a user tries to join a group, it shouldn't be a great performance cost to run a query to fetch the rule definition. It'll certainly be a lot less costly than trying to implement a rule parser and enforcer in SQL!

Another way of thinking about this is: a database stores data, not code. You can fudge this by storing the definition of rules as strings in the database, but let the database treat these as verbatim strings, not try to integrate them as code that the RDBMS can execute.

Answer 2

Thank you very much for the response! However last night or the night before I found this article: http://msdn.microsoft.com/en-us/library/aa964135%28SQL.90%29.aspx

This solved our issues. 1 query now in a stored procedure can tell us exactly which items are eligible.

-Josh