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

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 2

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.