On 6/4/07, Jeff Davis <pgsql@xxxxxxxxxxx> wrote:
On Sun, 2007-06-03 at 22:54 +0200, Alexander Staubo wrote:
> I agree with you and I don't; as it stands now, it's too hard to
> implement validation in the database alone, for the reasons I stated
> earlier. But I would love for it to be possible, so that I can be sure
> that not even plain SQL can screw up the data.
You're blurring the line between an RDBMS and an application.
Applications errors and database errors do not have a one-to-one
mapping, although they do usually overlap.
True, and when they overlap you tend to want to describe the
validation errors in one place, not two -- either the database or the
app, not both. Relational databases have traditionally argued that
these rules should be in the former, so that there's one layer through
which every single change has to go.
There are times when one database error maps onto several possible
user-level errors; and when many database errors map onto the same
user-level error; and when one database error does not cause any
user-level error; and when something that is a user-level error might
not have a matching constraint in the database at all. Trying to equate
the two concepts is a bad idea.
I agree. In my experience, however, the best kind of data model is the
one that is immediately mappable to user-level concepts -- to human
concepts. A "user" relation has attributes like "name", "birth_date",
etc. If you manage to keep the model flat and friendly enough, you can
map the attributes to forms and translate attribute-level errors
directly to form error messages.
In the cases where a user-level attribute is represented by a set of
columns, or a referenced relation, or similar, you provide simple
shims that translate between them. For example, you probably want to
store date-time attributes as a single "timestamp with timezone"
column, but offer two fields to the user, one for the date and for the
time. With Rails this kind of shim is simple:
class User < ActiveRecord::Base
...
validates_each :human_birth_date do |record, user, value|
record.errors.add(attr, "Bad date") unless MyDateParser.valid?(value)
end
def human_birth_date
birth_datetime.strftime("%Y-%m-d")
end
def human_birth_date=(date)
year, month, day = MyDateParser.parse(date)
birth_datetime = Time.local(year, month, day, birth_datetime.hour,
birth_datetime.minute)
end
end
With a well-designed, normalized schema, mapping relations and their
attributes to user input is very easy. I would argue that if mapping
is a problem, your schema is probably to blame.
The application has much more information about the user and the context
of the error that the database shouldn't have. For instance, the
language that the user speaks might affect the error message.
Localization is easily accomplished by piping the error message through gettext.
Some user errors don't have a corresponding database constriant at all.
For instance, how about a "re-type your password here" field? That
should cause an error if it doesn't match the "password" field, but the
database would have no matching constraint.
That's a user-interface detail, and not a data model detail; a
re-typed password has no database counterpart. I am speaking purely
about invariant constraints on the data itself.
Alexander.
