Re: Email data type

On Mon, May 17, 2004 at 09:21:54AM -0700, Steve Crawford wrote:

> Along those lines a(at)foo(dot)bar(dot)com and a(at)[foo(dot)bar(dot)com] are valid but they
> don't necessarily refer to the same mailbox (depends on the mx for
> foo.bar.com)

I don't believe the latter is actually valid, as it has to be an
address literal inside the square brackets. Until that's extended
by RFC that means IPv4 or IPv6 literals.

It's another example of "this is neither trivial nor well-defined",
though.

> > I'm not entirely convinced that an email address is a simple and
> > well-defined enough datatype to handle comprehensively within the
> > DB. The validation decisions are complex and vary from application
> > to application.
> >
> > (I use two text columns - localpart and domainpart, with an index
> > on reverse(lower(domainpart)) and leave validation to the
> > application, myself).
>
> Indeed. A problem with the "email address" datatype is that it hinders
> normalization:

Yup. That's a bigger concern than the overall vagueness of the problem.

> "Joe User" <joe(at)user(dot)dom> is valid but a database designer would
> probably prefer columns for name and email, or if the addresses were
> all people, firstname, middlename, lastname, email.
>
> As you mentioned, the email can be broken into localpart and
> domainpart but if the app requires it, the domainpart could be
> further rendered into toplevel (so you could find all the .gov or
> .edu), secondlevel (at least corresponds to a registrant) and
> subdomain(s) as necessary.

That's why I index the domainpart on reverse(lower()) - that way I
can say WHERE reverse(lower(domainpart)) LIKE reverse('%.com')

Cheers,
Steve