The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Buiness Rules as SQL in database!!??

My colleague has suggested that we store date related business rules as sql in the database.
an example would be:

"select days_to_D from rule_table where (product_id=123 and factorA='Y' and factorB='Y" .... ) "

The part in paranthesis would be stored in the database.

Does anybody here have prior experience doing it? What are the pros and cons?

I personally would prefer the application layer to build the sql for me based on data from the rule table.
Urumi
Thursday, June 28, 2007
 
 
why not make product_id, factorA, factorB parameters that you inject into a stored proc?

That way you keep the data retrieval logic close to the data, and abstract the business logic into an application tier (hopefully not the UI:)).
stephen
Thursday, June 28, 2007
 
 
There's nothing wrong with storing "rules" per se in the database when you define rules as human readable process workflow steps. For example, you might save a rule indicating that if the incoming shipment contains hazardous materials, notify the environmental safety group.

As far as I know, there's no inherent value in saving SQL statements directly in the database. Under normal circumstances, this requires two database queries, one to get the statement, and then a second trip to execute the statement. If you do the logical thing and cache the statement in the application, then you might as well do the entire thing in the application.

You could present the argument that you want to save the application configuration files in the database, and load them just once. The database offers few advantages over flat script files, and doesn't solve the problem of defining the database connection to begin with.

I also think it's easier and safer to define and save data specific rules (as in "productid = 123") by designing the schema accordingly. Under some circumstances, the database engine can then both parse and execute the rule in the same pass.
TheDavid
Thursday, June 28, 2007
 
 
The problem with this type of approach is the "FactorA, FactorB,..." nonsense. You will never be able to create a truly flexible rules system from this approach. Business rules by nature are typically way more complicated than what you can model from a simple SQL statement. And when you hit the need to support such complex rules you end up having to program workarounds which exponentially complicates the system. There is nothing worse than a table driven rules system that has countless band-aids in place to handle all of the things that the table based approach is too simplistic to handle.

People who create systems like this have good intentions but typically lack the foresight to understand the future needs of the system. If I had a dollar for every system I've had to work on where we ended up scrapping ideas like this I'd be rich.

Business rules should go in code because that is the only way that you will be able to handle future requirements easily. Why limit youself now by placing pigeonholing your system?
unknown
Thursday, June 28, 2007
 
 
I'd also like to point out that this will end up being a configuration management nightmare. When business rules are in code they are easy to version control and debug. But when they are in a database you end up having the additional complexity of having to version control the database contents which gets messy.

I've worked with systems that had hundreds if not thousands of business rules in database tables and it was a never ending battle trying to make sure that all of the developers, test systems, and production systems were running with the same database entries. And this type of thing is nearly impossible to debug. You basically end up just blowing everything away each time and starting from scratch with scripts. But at the same time, you end up blowing away any changes/personalization that users may have made.

So my advice is to run away from designs like this. No matter what anyone else tries to tell you (especially DBA's), databases are for data. Code is for business rules and behaviors.
unknown
Thursday, June 28, 2007
 
 
Unknown,

The version control issue also applies to stored procedures.
frustrated
Thursday, June 28, 2007
 
 
+1 to the poster who said:

"There is nothing worse than a table driven rules system that has countless band-aids in place to handle all of the things that the table based approach is too simplistic to handle."
Greg Send private email
Thursday, June 28, 2007
 
 
"The version control issue also applies to stored procedures. "

True. But I didn't say anything about stored procedures so that is another issue.

There is a difference between version controlling the schema or the SP's/views and trying to version control the actual data in a database. The former typcially already has scripts in place to recreate them from scratch and you don't expect the common user to be going in and changing them. The latter may or may not have scripts depending on how dilligent you are and it is much easier for people to go in and tweak table data without telling you.

It's just another point to consider.
unknown
Thursday, June 28, 2007
 
 
A while back I worked with a company who faced a similar problem and can share some direct experience on the subject.

Background: This group had a large, highly configurable web-based software application that effectively let users create complex, multi-step input forms. You were allowed to setup all kinds of interesting rules, such as "Maximum amount for field ABC is 2,000, unless field DEF is between 500 and 600 inclusive, then maximum amount for ABC is 3,000". Interestingly enough, they had a powerful form- based interface you would use to define such rules to create *your* forms.

Before I came along, they had written code that would take whatever you created and compile it into a stored procedure, which would then be literally *inserted* into a big text field in the database! The stored procedure was removed, created, exectuted and deleted each time a rule was required. This kept their database from filling up with compiled code but obviously slowed down the application.

Anyway, I helped them design a table structure that would capture a set of rules for a particular form so that it could be checked with a single generated SELECT statement. The resulting system was basically a truth table combined with comparison types. This ensured that if they needed to expand the ruleset in the future, it would not be too difficult.

The moral of the story is that it's not a terrible idea to have business rules included in your database, so long as they are included *as data* and not *as code*. Anything that has to be pulled out of the database and then manipulated is going to create nightmares. Don't be afraid to put more data in your database, that's what it's good at!

Good luck.
Robby Slaughter Send private email
Thursday, June 28, 2007
 
 
I think that Robby's post points to something that I really skipped. It is important to determine WHY you want to store rules in a database. And then you need to define what you mean by "rules". In Robby's case, he is talking about storing fairly well defined form data validation rules. I'm talking about rules that are more along the lines of procedural business rules thatt are based on other factors in the system. The OP may be talking about something else entirely.

I think one way to look at it is that if you are storing rules in the database to facilitate the user being able to define their own then that is probably fine (how else could you really do it besides using some other form of persistent storage?). But if you are storing rules in a database because you think that it is a clever or more flexible way to implement something and save a bunch of coding then you are wrong. Nothing is less flexible than forcing yourself to create rules to fit a static table structure.

So storing rules in a DB certainly has its place. Just be sure to understand WHY you want to do this before choosing to do so.
unknown
Thursday, June 28, 2007
 
 
I agree that the basic question is "why do you need to store rules in the DB?"

Do you envision some tool allowing business users to build rules "on-the-fly" (I do hope you don't expect them to manually create them with bare SQL inserts)?

Do you think this is more flexible (e.g.: you sell your final product to different customers and want to avoid/reduce code branches and having to mantain multiple versions)?

Do you need to do this to leverage an unbalanced skill pool (e.g.: we are all PL/SQL gurus but the main app is in Java and George is the only one who understands it so when a rule changes he is the only one who can do the job)?

Depending on your actual needs (and I may have missed them, these were just the most common cases) you may consider addressing the problem in a different manner. And have a look at stuff like Jrules or other rule engines.
I think there are some free ones out there, so you can at least evaluate the idea.
Paolo Marino Send private email
Friday, June 29, 2007
 
 
Sorry, forgot a possibly useful link:

http://en.wikipedia.org/wiki/Business_rules_engine#Business_Rules_Engine_Providers

They list free rule engines for Java, C# and possibly other setups.
Paolo Marino Send private email
Friday, June 29, 2007
 
 
I love putting business rules in the database directly, that's my bias. PL/SQL is as robust if not more robust than Java (IMHO) at this point, I've had a lot of success with it.

Like everything else in life, YMMV. My apps were tailor made for dynamic coding.

I started a long discussion on this very topic a few months ago, btw.
Steve Hirsch Send private email
Sunday, July 01, 2007
 
 
We have C# product (sort of ERP) that now works with Oracle and SQL server.
My arguments against:
 * stored procedures have their own language, set of rules and quirks. I need language expert for each database if I want to fully exploit posibilities.
 * poor database compatibility
 * you need new development environment for each database
 * there are new compilers and debuggers in the game
 * you need new testing framework (if you want to use any sort of unit testing)
 * process automation gets more complicated
 * version control gets more complicated

Possible arguments for:
 * could gain better performance

I do not see any significant gain in using stored procedures in our current product. Our current users create from 0.5 - 10 milion records a year in database, and we do not have any performance bottlenecks.

One place I would consider stored procedures is data mining product that we are planning to build. It will do some intensive calculation over existing data and then it  will create new records in statistics database.
moronica
Sunday, July 01, 2007
 
 
I would caution against storing this type of information in the database as it can lead to an excessive amount of database traffic and database CPU cycles.  Also, as noted before, a simple table driven design never stays simple.  The  interactions between tables stored in the database and exceptions in the code become a maintenance nightmare.
Wayne M.
Monday, July 02, 2007
 
 
Looking at the example in the first post, it looks like you're just putting the where clause (or just part of it) into the table. Why? If it's because your business rules are data driven, and can't be determined algorithmically, that's probably the way to go. (I've also seen applications store their report definitions in a database. That way you can push out a new report without deploying any new code.)
In one of my current apps, I've gone both ways. One data import section stores SQL in the database to get the data that it needs, which is just a date and an id. The SQL can be pretty complicated in some cases, but it works well, because what it's trying to do is simple from the perspective of the client app.
The other import section stores it's configuration in the database, but runs as application code. It's trying to determine the state of the data in the source systems, so it uses a fair amount of logic that really belongs in the client/middle tier.

Hope that helps.
Mike Swaim Send private email
Monday, July 02, 2007
 
 
Well, usually all the people using java/c#/... and never been database guy, think about the DB as a black box that at a maximum stores data, but is for sure the slowest component in the architecture.

All the database guys knows on their skin that usually the slowest side is the middle tier (if any).

In my opinion, if you are using an Oracle DB, go for storing the Business logic in PL/SQL stored procedures and call them from your application.
The T-SQL used in sql-server is less powerful but can be used too.

If your DB is not Oracle or it has not the possibility to store procedures (or complex sql), go for storing the sql statement in the application: be sure you have a very good sql developer and a good dba to tune indexes, queries, etc.

My 2 cents.

P.S.
All the time I hear about not be chained to a DB, I would like to remember to all the programmers that what is important IS NOT the application but the DATA (if the company loses the DATA, well the game is over) and that the possibility that your application will be changed in a few years is really high if compared to the possibility that the company will switch its database.
alessandro orsi
Wednesday, July 04, 2007
 
 
The database is easier to optimize and SQL is a higher
level language more suited more modeling business rules.
Also, by judicious use of stored procedures you can
reduce the round trips to the database by executing
basically all the logic in the database. That is
generally my preference. However, in practice it seems
there are issues with version control of stuff in the
database. So, if you are only running a single system,
I would suggest you to push the logic into the database.
If you have a good version control system for your
external code, and you have multiple databases that you
must keep synchronized, push the logic out into the
external code.
Object Hater
Thursday, July 05, 2007
 
 
"However, in practice it seems
there are issues with version control of stuff in the
database."

Why is that? Why can't PL/SQL code be stored in version control systems?
Steve Hirsch Send private email
Thursday, July 05, 2007
 
 
"Why is that? Why can't PL/SQL code be stored in version
control systems?"

This phenomenon has arisen in my experience, which is why
I qualified it with "in practice", rather than being a
fundamental theoretical difficulty. All the places I have
worked that have had top notch dev teams have had flawless
db source control.

Therefore the explanation must not be because SQL can't be
version controlled if everything is going well; rather it
must be that it is easier to screw up a database than code
when things are going badly.

That is, if you find yourself in a situation where you have inadequate controls, inadequate process and the other devs have inadequate understanding of dbs, it turns out
that the databases are easier to abuse than the code.

My theory is the following:

Now it's a lot harder to make a change at all to an exe
because you have to go through a build. Then it's
harder to affect others because you have to distribute
the bad exe away from your machine; both the distribution
system and the build process may be integrated with the
source control somehow, making it harder to circumvent.

However, you can instantaneously implement a change to
the logic in a database without ever needing to touch
source control. You thereby also affect every user of
that db. Hell, if Knucklehead the Developer really wants
to he can do rapid development on a live production
server.

(Don't laugh, I have seen this done. I have even seen it
listed as a job description on job sites.)

This would make the source control of databases harder
from an engineering point of view, even if the root cause
is a human factors issue. And because the source control
is harder, it is harder to keep them in sync.

In the interests of science and better understanding
I invite attempts to shoot down my theory (so that I can
try to plug any holes you find in it and thereby come up
with a watertight understanding of the phenomenon).
Object Hater
Friday, July 06, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz