Re: multimaster - Mailing list pgsql-general

On 6/4/07, Jeff Davis <pgsql@j-davis.com> 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.