Put Your Business Rules In Your Database

by Curtis Poe

Maybe you know how to drive a car. Maybe you can fix the carburetor. You probably don't assume that you know how to design the car. However, programmers who can write huge SQL statements and understand the output of an EXPLAIN statement often assume that they can design a database. Admittedly, unless we're comparing Oracle 9i with the 1967 Volkswagen Beetle, databases usually aren't more complex than cars, but a properly designed database can be very hard to come by yet it's the key to a solid application.


8 Comments

Aristotle Pagaltzis
2007-08-14 13:45:12

Ewww, triggers. Last resort. Last resort.


You don’t need them.


Get rid of priority 3, make the priority column nullable, and make a unique index over job_queue, task, priority. Then you can have exactly one row each for priorities 1 and 2 for any given job_queue, task pair and any number of rows with NULL priority for the same pair.

Paul Browne
2007-08-15 07:42:07
I view this as very dangerous (and that is toned down fromt he words that I was originally going to use). Just because you can build it from a technical point of view, doesn't mean that you should.


A couple of questions:
- What's your defintition of a business rule? If it's data integrity (as are most of the 'rules' that you list) then yes it belongs in the database, but it's not a business rule as most people would understand it.


- My definition of a business rule is a piece of logic that can be articulated by end users that is reviewed and changed frequently. For a larger database, how is it possible to review 'rules' once they are scattered throughout the database?


I don't mean to come across as too negative as at least you have the courage to suggest a way of doing things. Likewise, the approach I prefer is at http://www.oreillynet.com/articles/author/2366


In summary: I don't use a database to run rules, and I don't use a rule engine to store data.


Paul

Ovid
2007-08-15 08:42:24
Paul: I understand your concern, but many business rules should be relatively invariant. For example. maybe a customer's required to have an address and maybe they aren't. That's the sort of rule which tends to be an invariant (but read on). Business rules which change little should be reflected in the database layer to ensure that you're not dependent on the applications "getting them right". Personally, I'm tired of fixing bugs caused code in language X getting the business rules wrong because they were implemented in language Y. There are plenty of ways this can come about and it's not always easy to share this knowledge outside of the database. Of course, once it's pulled from the database, no code is then required to respect it. It's a very common source of bugs.


On the other hand, you also have application rules. Those should generally not be in the database. This is application logic and control flow. It's similar to the difference between the "model" and "controller" and an MVC framework.


If you want to keep the invariant rules out of the database, that's your choice, but it hinders complexity management. Of course, some people will claim that there are no invariant rules and this is true. But that's why we have test suites and learn to refactor databases the way we refactor code.

Paul Browne - Technology and People
2007-08-16 00:30:43
@Ovid


I think the key here is 'what is a business rule'. To me the sample you give (customer requiring an address) is a data integrity rule and should , without doubt , be in the database. You are correct that these rules do not change too often.


To me , a business rule is more 'when the customer makes an order of more than 10000, a credit check needs to be run'. These do change and need constant review. I tend towards using a Rule Engine for these (and normally JBoss Rules) as


- The rules are written in a near english language
- The rules can be accessed by many different applications (no risk of differing implementations)
- The rules can be versioned and stored in a database (as data, not as db code)


Paul


Ovid
2007-08-17 02:12:55
@Paul: the reason the "should addresses be in the customer table?" question arises is because it reflects a business rule. Two similar sets of data can have different normalization requirements based on business rules.


One company I worked for wanted to send out a mass mailers to their customers. That was pretty easy. We selected the customer name and address from the customer table. Later we were having a huge sale and wanted to bring in new business and we wanted to send out mail to every addresses, regardless of whether or not we had a customer for the address. We had to pay a lot of money to an outside company to collect this data.


For that other company, they have customers (us, for example) who are associated with addresses, but since address would no longer be dependent on customers, the addresses would properly be stored in another table. Proper normalization is a reflection of business rules. However, it can't be perfect, as in the case of my company's database not supporting this one-off need). If we found ourselves doing this regularly, it would mean the nature of our business has changed, our business rules have changed, and we would then have to determine if refactoring the database and maintaining the address list would be a better solution than buying the address list from another company.

Ovid
2007-08-17 02:18:55
@Paul:


I forgot to mention that I do agree that it can be difficult to store all rules in a database (particularly when working with older database software). However, if given the choice, I'd at least prefer to put them in stored procedures to ensure that code run against the database is unlikely to subvert said rules. How can you protect your data when the guy down the hall is writing a quick hack to "fix things"? How do you protect your data when you have a huge database and another large project needs access to it, but they're using a different programming language?


Pulling this "must obey" knowledge out of the database means that someone using the database can easily ignore it and is likely ignorant of parts of it.

Rajgo
2007-09-18 00:15:58
Hi Ovid,


I am a little late is discovering this post, by here goes anyways.
Whether you want to have your rules in your DB or externalized using a rule engine is a design choice that depends on what kind of IT team you have, and what your business guys need.


Even considering the "quick fix hack angle", I think you will have more long term testing issues with the db way, than with the rule engine way. Many customers and evaluators that I have come across raise testing and maintenance as a major reason for choosing to use rule engines.


Even in the case where multiple systems run on different programming languages, I would suggest using the rule engine as a Web Service (popular term, Decision Service), and then letting clients use it.


My 2 cents

SEKE
2008-02-19 02:05:58
As you can notified, I am a student in Higher Degree Computer Science and I am building my project in Database specially by designing one of the college registration Database but actually, I am stacked because of the business rules of my database and I have checking through the internet to find out how people could help but no answer, so I need your help to assist in what I am doing.
Do not hesitate to contact me anytime even by my cellphone number at 0027725513954 or e-mail me by the above e-address.
hoping that I will get the answer from you as soon as possible,wish a wonderful journey!
Regards