Re: How much of data constraints on database is enough?

On Jan 16, 2008 9:42 AM, Fred Moyer <fred(at)redhotpenguin(dot)com> wrote:
> > I agree w/ the all the above.
> >
> > The database needs constraints to prevent inconsistencies being
> > introduced by external clients (i.e. not going through the main
> > application.) Its way to easy for db admins to corrupt the data.
>
> Agreed, anyone who has ever had to uncorrupt data knows that it is
> absolutely no fun.
>
> I usually add as many database constraints as the data model looks like it
> needs, and don't worry about performance considerations until my profiles
> say that the database is the bottleneck.

Indeed (profile before optimizing, blah, blah, blah).

First, you are really screwed if your data is corrupt. Two, the more
work done upstream (and the DB is pretty far upstream) the easier for
application level development. Three, good database constraints make
testing the application layers much easier, as bad data will sometimes
discover parts of the application layer that aren't figured out yet.
Four, one can arrange "try/ catch" blocks in the application code to
handle the DB constraints as part of an input system and not even
worry about checking data in the application level, though this always
strikes me as a little hackish and only useful until one actually
figures out input checking in detail.

>
>
>
> > Constraints only in the application are too easily circumvented,
> > either by ignorant application developers or db admins. Inconsistent
> > corrupt data is just too painful to cleanup, and its so easy to
> > prevent w/ a real DBMS that it seems foolish to turn it off. Its like
> > compiling C/C++ w/ warnings disabled. Why shoot yourself in the foot?
> >
> > I like application-level constraints as user-friendly validation
> > feedback. For example, Rails (or WebObjects, etc.) provides consistent
> > user-friendly error messages for validation errors ready to display or
> > parse by non-human clients.
> >
> >
> > All this being said, I am not happy w/ having to maintain two parallel
> > sets of constraints. It violates the Don't Repeat Yourself principle.
> > I'd like some DDL to generate schema definitions with constraints as
> > well as be picked up by the Rails application code.
> >
> > Drew
> >
> > On Jan 16, 2008, at 6:24 AM, Josh Berkus wrote:
> >
> >> Miho,
> >>
> >>> Being said that, you also have choice to do the same in your
> >>> application as well. Why do you want to have them in 2 places? Maybe
> >>> some constraints are appropriate to be done in application, some
> >>> should be on database. But where do you draw the line? Might as well
> >>> do them all in application!?
> >>
> >> The answer is that data integrity, like security, is a process, not
> >> an end
> >> result. To extend the parallel further, only checking data
> >> integrity in your
> >> input code is like implementing network security by having only a
> >> firewall
> >> and no internal security. That's a strategy called "perimeter
> >> defense only"
> >> and is justly attacked within the security community; all it takes
> >> to defeat
> >> it is one employee with a virused USB stick. Likewise, if you
> >> *only* do data
> >> integrity in your interface or ORM code, then you risk having some
> >> employee
> >> dash off a bit of careless SQL, or one bug pop up in your ORM code,
> >> and
> >> irrecoverably corrupt your whole customer database.
> >>
> >> Sure, it's more work to have data integrity checks in the input
> >> code, the
> >> middleware, table constraints, and triggers. But it's also more
> >> work to have
> >> firewalls, passwords, multilevel permissions and secure network
> >> protocols on
> >> your internal network too -- yet any smart company does all of these.
> >>
> >> --
> >> Josh Berkus
> >> PostgreSQL @ Sun
> >> San Francisco
> >
> >
>
>
>