Business rules in the repository?

Question

Let's assume that an application has all necessary business rules in the model / presentation layer and that they work fine. My question is one of whether or not redundant business rules (i.e. a span of two dates cannot overlap any other existing spans) should be used in a repository like SQL Server.

Is it necessary/beneficial to add a constraint in SQL Server that enforces this rule? On the one hand, it prevents anyone (including DBAs) from inadvertantly breaking business rules when they bypass the application. In addition, we already have some forms of business rules in the repository via primary and foreign keys. On the other hand, the duplicated rules require additional time to develop and maintain.

This question spans many different technologies so I intentionally kept the tags generic.

Answer 1

Depends on your application - keep the logic in the application code if it's intended to be db agnostic. Otherwise, the business rules are better suited in the database.

Answer 2

You said it yourself... what enforces the business rules on the back door (i.e. the DBA poking data). If you fear this will happen, then pay the price. If not, then leave it off so the rules enforced elsewhere will not be redundant.

Answer 3

You will generally find it very difficult to maintain business rules in multiple places by hand.

I have made good use of code generation on some projects to generate some types of rules from requirement models. For example, I might have the requirement "First name shall not exceed 50 characters". I model that requirement in UML in a structured manner, then generate UI code to cap the input at 50 characters, business logic to enforce that same limit (never trust the UI!), and DDL/ORM mapping file to specify the column width in the DB.

That same idea could be extended to model more complex rules, and generate appropriate enforcement code into each layer of the application.

Answer 4

If you care about your data, you will put the required rules there in preference to anywhere else. Data is affected from many places beyond the applciations. Putting rules only inthe business layer is short-sighed and leads to serious data integrity issues atat can be horrible to try to fix. Rules concerning the data, belong in the database.

Answer 5

Business rule or data integrity rule?

My experience (mostly large corporate, we're not talking software house or hobby shop here) is that your database will outlast the application. And eventually have other apps talking to it. And someone, somewhere will break the database if it has no rules (of whatever kind) in place.