Registered: Sep, 2003
Re: Database Constraints: Use Them or Lose Them?
Posted: Oct 2, 2009 11:04 AM
> > This simple example can easily be handled to avoid
> > duplication by creating a table in the database layer
> > columns min_salary, max_salary, effective_from and
> > effective_thru. The application can query the database
> > these values, cache these values, and everything is
> > care of. DRY is not violated because the fact of the
> > minimum salary and maximum salary with applicable dates
> > kept in only one place, the database. The application
> > layers don't have these values but get them from the
> > database when needed. There is no performance issue
> > because the data is cached at the application layer.
> In the context of this discussion, that's implementing the
> constraints in the application layer, not the database.
I half agree. The issues are simply whether:
- data gets corrupted on the wire, thus application editing is not sufficient
- constraints get updated, thus both the application(s) and the database have to be maintained
- there is the ancillary issue of whether constraint changes will be applied to existing data, or only apply forward going. for this one, it has to be done in the database
Providing client side editing as a helping hand is fine and good. Such editing should be driven by the database constraints, however defined there. Generating (manually or automated) client side code to manifest those constraints leads to the conflict. The coders want the application(s) to be the constraints of record. Database wonks want them in the database, as Dr. Codd intended; this approach foreshadowed the OO design principal, which is to say data and its methods are co-located. That OO folk elide this hypocrisy is a source of constant amusement.
Should we reach the nirvana of infinite bandwidth, the coder folk no longer have a leg to stand on; we have a GUI-fied VT-100 reading the keyboard clicks, asking the database whether the click is good or bad, and proceeding accordingly. Yum.