Re: where to divide application and database

On Thu, Feb 19, 2009 at 11:43:19PM +0000, Sam Mason wrote:
> I was just reading over a reply from David Fetter from a couple of
> days ago; the thread is archived[1] but this question doesn't really
> relate to it much.  The a question about how to arrange tables and
> David make the following comments:
>
> On Tue, Feb 17, 2009 at 09:53:00AM -0800, David Fetter wrote:
> > On Tue, Feb 17, 2009 at 04:40:58PM +0000, Sam Mason wrote:
> > > >      user_name varchar(50) NOT NULL,
> > >
> > > As a general design question; should user_name have a UNIQUE
> > > constraint on it?  i.e.
> > >
> > >   user_name VARCHAR(50) NOT NULL UNIQUE,
> >
> > Yes, it's good to have a UNIQUE constraint, but not this one.  To
> > have a sane one, it needs further constraints, and in 8.4,
> > case-insensitive text (citext) type.  Here's one that is
> > reasonably sane until citext is available.
> >
> > user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR.
> >
> > then later:
> >
> > CREATE UNIQUE INDEX unique_user_name_your_table
> >     ON your_table(LOWER(TRIM(user_name)))
> >
> > You might also require that whitespace be treated in some
> > consistent way, one example of which is simply forbidding
> > whitespace in user_name at all.  This you can do via CHECK
> > constraints or a DOMAIN.
>
> The reason behind this appears to be moving some of the checks into
> the database and away from the application.

Since a useful database has *many* applications instead of "the"
application, I think this is an excellent move.  Single Point of
Truth and all that.

> When I've solved similar problems before, I've tended to make the
> application more aware of what's going on by having something like:
>
>   user_name VARCHAR(50) NOT NULL UNIQUE
>     CHECK (user_name ~ '^[a-z][a-z0-9_]*$')

My point there was that simply limiting the length isn't enough for
many purposes, and when you're adding DOMAIN or other constraints on
the value, that's a place to put the length checks in, too.  For
example, you might well want to set a lower bound on the size of a
user_name, not just an upper bound.

> I don't think that either my nor David's is better in general, they
> apply to different situations.

I don't even think they're *different* in general ;)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate