Advanced Database Features | 111As with any other area of data modeling, there are gray areas. An example would be
“an employee’s salary must be positive,” which could conceivably go either way.*
The advantage of constraints is that they narrow the domain of possible results the
database can generate. When you know the DBMS for an online store can never out-
put a negative price for a product, you can sum the prices for the line items belong-
ing to an order without worrying about invalid prices. Though the line is drawn in
different places for different applications, the basic principle is this:the database
should not enforce business logic, but it should enforce consistency and integrity.
Regardless of differences of opinion on check constraints, one type of constraint is
non-negotiable: foreign-key constraints. If a foreign-key relationship is required, an
unassociated record is semantically meaningless and must not be allowed to happen.
It only makes practical sense to formalize that association.
The only truly robust way to ensure that a database maintains integrity over years as it
accumulates data (as databases tend to do) is to declare appropriate constraints on the
data. Unless you can say for certain thateveryapplication or person accessing the data-
base will do so through the domain model (going through all associated validations)
every time, the only sensible option is to treat the database as an integration database.
There is a bonus to providing constraints: typically, the more constraints provided
on a database, the better job the query optimizer can do at creating a query plan.
A common complaint about database constraints is that they require you to specify
semantic information in two places: your database and your application code (you
usually want to trap invalid data in your application’s validations before attempting
to insert it into your database, even if the database would catch the error anyway).
The DrySQL library†goes a long way toward removing this duplication. It infers the
schema relationships and validation rules from the database’s types and constraints, so
they don’t have to be specified in the application. DrySQL works with all of the major
DBMSs: PostgreSQL 8 and up, MySQL 5 and up, SQL Server, Oracle, and DB2.
With DrySQL installed, you can simply require the library in the environment con-
figuration file:
require 'drysql'Then, all that is needed is to inform ActiveRecord of the mapping between tables and
model classes (even that is not necessary if the tables are named according to the
defaults):
class Client
set_table_name "customers"
end- I would probably keep that one at the application level, because it contains the business rule that no
employee’s salary is zero. However, “an employee’s salary must be non-negative” would most likely be an
integrity constraint, as it is nearly inconceivable that you would “pay” an employee a negative salary.
†http://drysql.rubyforge.org/