THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Business Rule Enforcement

I know this is a topic that could cause a few heads to spin around uncontrollably if taken the wrong way, but I preserve.  Today I was having a conversation with an end user, an influential one at that, about a business rule that was needed for the time entry system we are putting together (and no comments on make/buy decisions, no way I could come down on either side of that discussion and please all of the real people I work with.)

Anyhow the rule was that the maximum number of regular hours a person could record was 168 and OT was 128.  In other words, 7 * 24 or (7 * 24) – 40.  This is a very important rule to put into your system to prevent someone from accidentally putting in an amazingly large number of hours and likely getting paid for it (before having to give it all back of course.)

But this is clearly not the final rule on number of hours, right?  I mean, you certainly don’t usually want a user to record 168 hours, right?  I mean, I have worked a long week or two in my life, but never have I approached 168 (around 80 is the place where the magical pixies arrive to do a good amount of my work for me, though they write in a gibberish that I can’t quite seem to translate once I have slept.)

So I starting thinking that there needed to be some standard way to describe the different levels of business rule enforcement.  For example:

  • Hard and fast – These are rules that will never be broken under any circumstances.  Like an exact birthdate in the future.  Or a negative amount of time worked (reversing entries could be an exception, but you would tag a row as such if needed.) Working more hours in a week than physically possible is another.
  • Abnormal range – This is a  band of values that are not normal, but they are not necessarily wrong either. Overtime might be an example.  For non-exempt employees, you might warn them “hey, OT is not usual, are you sure,” which, loosely translated to manager speak is “bozo, if you put in for OT pay and you didn’t actually work OT, I am going to pound you”.  You might even use some form of predictive algorithm for this, like if this is the first time they have ever put in OT, a gentle warning might be raised when saving the time sheet.
  • Approval range – This is the level where the user is not empowered to make their own decisions. For example, take 168 hour week. I am pretty sure that this value (for the average user) would require some level of extra management approval. If not, a person could file a few of these doozies a few weeks before they quit and make a mint, assuming no other part of the process has implemented checks for abnormal ranges that is.

The question then becomes how to implement these rules.  And this is where the battle begins to get ugly doesn’t it?  Business rules in the database, the horrors!  Well, ok, let me just say *my* feelings on the subject and feel free to chime in whenever.

  • Hard and fast – These rules ought to be in the database layer (at least) using constraints (unique, primary key, check, foreign key) and in some cases triggers (not so much stored procedures.) Not doing this leaves you open to some goober (dba like myself) with SSMS coming in and “fixing” the data in a way that was not exactly intended.  It also makes 100% sure that data protected with constraints (that are trusted, anyhow) will not need to be checked again upon usage.  Too much code in screens and ETL process is essentially restating the very common rules that should never be broken.
  • Abnormal range – These rules should stay out of the database entirely. While it is good for the UI to give the user the hint that they are being dumb, if there is any chance of a value being correct, then it is just too much trouble to validate it at the database layer
  • Approval range – The approval range is the shared responsibility of the business layer and database too.  The database at the very least needs to record that an authorization was gotten to ignore the system business rules, but in most cases, what that range was at any given time is probably best left a mystery to the database.  Just like in the abnormal range, your screens and ETL processes need to know that the data is within the realistic realm, but certainly shouldn’t toss chunks when it hits the data.  Using overrides in your data warehouse to understand data, and possibly just tossing out overridden data may certainly be a possibility for queries/processes (It might be that the customer says to use some out of the normal data, but for normal calculations you only use “regular” readings.  Who knows, clients do a lot of stuff that makes no sense to us programmers, but it is their data!)

Proper separation of application logic and data is essential to building a great/robust solution, but what proper is can be a matter of contention.  Just remember that the data is the reason for 99% of all business apps. Data on who ordered what, data on how they paid, and analyzing that data to get them to come back and repeat the process (and tell their friends!) The more hard and fast rules you can validate, the more likely that your data assets will be safe from ignorant mistakes (by a programmer, at least.  Can’t stop a user from spelling Toaster : C-h-i-c-k-e-n. Well, at least not until I finish developing my mind probe. Shh, don’t tell anyone.)

Published Monday, February 16, 2009 11:55 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Alex Kuznetsov said:

Hi Louis,

This is a great post. I cannot agree more. If I understand the problem correctly, as long as the business rules do not change over time, something like this should work:

CHECK(Hours<168 AND (Hours<80 OR ApprovalId IS NOT NULL))

where ApprovalId refers to a row in Approvals table. However, in many cases business rules do change over time. Yet it is still possible and feasible to implement the business rule using constraints, but it is not trivial.

February 17, 2009 1:33 AM

Hugo Kornelis said:

Hi Louis,

Great post, and I agree on most of what you say.

For "appproval range", I always try to convert this to "hard and fast" by sucking a bit more of the business process into the database. Sticking with your timesheet example, let's assume the business rule is that every timesheet over 50 hours needs managerial approvement. My choice would be to add an attribute "NeedsApproval" to the timesheet that will be automatically derived when a timesheet is inserted or updated (in a trigger). Plus of course the attributes to hold the aproval result (approved/denied), approver, and approval timestamp.

The paycheck logic would then disregard all rows still waiting for approval (either skipping them completely, or paying the standard 40-hour workweek, or paying the approval limit of 50 hours - that would have to be a business decision). Warehousing apps should make a similar decision. In many cases I prefer to think of an unapproved row as an uncommitted transaction (i.e. treat it as non-existant until it has been approved). In this specific case, aplying that logic to the paychecks would probably be unfair - work lots of overtime and then don't even get paid the reglar hours because your manager has to approve the timesheet first... :)

February 17, 2009 4:09 AM

Dave Jermy said:

The only thing I disagree with is not storing the range for a Needs Approval value.  If the range varies over time (or, more likely in your example, dependant on the level of the organisational structure the employee is in), then any reports looking at timesheet records that need authorisation may need to show what the authorisation threshold was.

February 18, 2009 5:20 AM

drsql said:

Alex: Hours < 168 is a no brainer.  The approval thing starts to get tricky.  What if some users don't need approval?  And what if that number changes frequently? That is where it starts to get difficult.  I kind of figure if it won't bust external code, is it really necessary for the DB to check it?

Hugo: It is all of the tricky bits that get so difficult to enforce. I am struggling with that right now.  There are 20+ rules that govern the entry of time.  And no one really knows them.  And what of WHO can approve?  I plan to build a role based security system, but do I want to build triggers to make sure that it is approved by an approved approver? (whew, say that one 10 times fast). It all gets tricky and becomes this dba vs programmer struggle which I tend to lose because my solutions take more time up front (even if they save time later.)

Dave: You definitely didn't hear me say that exactly. I believe in data driving as much as possible.  So even if I don't enforce the rule, the db should be the repository of ranges/rules and such that allow the app to make sure that the rules are met. Part of the current projects problems lie with early hard coding of values, but once I was involved we have started to add rows to our tables (like if a project can have no more than 10 hours per week, that is an attribute of the procject table.)

However, reports are where things get mondo tricky.  Say the project now allows 12 hours per week, but last week 10.  People who put in the max time get a bonus at the end of the project. So people who start when the hours are raised look more dedicated than the ones who started earlier.

Now you have to decide how to deal with this.  Update the project? Use a new row for the project time limits (with expiration date)?  Relate time entry to the project time limit row?  What if there is not time limit. On the time entry store the percentage of allowed hours?

So data driving the current time version is easy. Making the protection code honor it now is easy enough... But going back and using old limits, or even changing old values gets tricky, since in the case of relaxed limits we were ok, but what if the limits tightened?  A major design goal of mine is that you NEVER have data in the database that you if you execute:

Update table

set    column1 = column1, ... , columnN = columnN

It won't fail. (and if you name your columns column1, that isn't good either.  Luckily you can't name your table table :)

Tricky business trying to implement tricky rules...

February 18, 2009 11:41 PM

Alexander Kuznetsov said:


I store changing settings in the database and use them along with ranges of their validity - works for me, might work for you too. Also maybe "some users don't need approval" can be implemented as some users have blanket approvals with very high limit, valid from the stone age all the way to the year 9999. Can it work out for you?

February 19, 2009 11:01 AM

drsql said:

I guess all I was saying is that there is a fine line between what you can do and what you ought to do in database code. I tend to want to do everything I can and store everything possible, but then I get beaten up by programmers who don't want the DB to do much of anything.  Joins, the horrors, I believe the saying goes :)

So what do you do? Implement a thorough (if a bit complex) system of approvals in the database?  Or leave some of that to the external powers.

You do bring up a good point I failed to. If you need to report on the data, or the history, or what have you, then it must be stored.  If your external processes rely on data being in a certain set of parameters, then you must store data about the rule that you are enforcing.  But if it is just a simple rule, and it might change based on the whim of a manager/personality at a company, leaving it to a less rigid layer of the application seems like a decent way to deal with the problem.  Of course, data driving the conditions is always good. I have saved myself many hours by making it where values, groupings, etc were simple updates to the database to enforce a new rule that followed the existing patterns.

February 20, 2009 1:34 AM

Agility Alliance said:

Good work!  I wanted to let you know we added a link to your article:

February 20, 2009 10:34 AM

Troy said:

Exceptions to rules are simply more general cases of rules themselves. This, I think, is the point that Alex and Hugo are driving towards.

My bias is to express rules as much as possible in the DBMS for the following reasons:

1) The declarative language available to us in T-SQL is more concise and less prone to error than the lower-level languages typically used in the application or (shudder) "business logic" layers.

2) Rules expressed as constraints and via time-interval-bound data values are more open to manipulation and derivation through queries. I've had to write code to parse through source control archives to try and discern when rule implementations changed in the app layer code, and it ain't fun. Much better to write a simple db query.


February 23, 2009 5:07 PM

drsql said:

Troy, I agree with you that whenever you are going to need to report on the quality of the data that storing it in data is essential. The key is to really understanding when that is necessary.

As database designers, we seem to be constantly in battle with the dev type to balance implementation.  A good understanding of what it means to protect data in the db or out is essential.  If you can really get it down in the requirements, you can then design it in the db with the blessing of management... Otherwise you are spending your time building complex layers of business rule enforcement that has more loopholes than can be easily managed.

In the end, it is important to find a way to put rules in the UI and the database, even if just the existence of the rule or overrided is what you store in the db...Good conversation, for sure.

February 24, 2009 11:18 PM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement