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

> 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.

> 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
>
>