[Vmail-discuss] Separate domain_alias table?

[Chris Lightfoot]

On Thu, Jul 10, 2003 at 05:28:41PM +0100, Paul Makepeace wrote:
> Hi,
> 
> CREATE TABLE domain_alias (
>   domain_name varchar(255) DEFAULT '' NOT NULL, # domain to redirect to
>   alias varchar(255) DEFAULT '' NOT NULL,       # alias for 'real' domain
>   PRIMARY KEY (alias)
> );
> 
> I was wondering whether there was a reason to have a separate table
> versus including it in the domains table?

Probably because we added it at quite a late stage-- after
we'd set up the rest of the system.

> I'm asking since I'm creating something similar-but-different and the
> extra work of normalizing doesn't seem to me to buy anything, at least
> that I've spotted :-)
> 
> Pros: only requires a single update, no "check if exists, insert if
> necessary", or additional selects (modulo left joins/unions).
> Cons: can't map one domain to many. Semantically this isn't possible
> though, right?

Um. We allow that (one domain having many aliases). This
is quite useful when somebody has example.{com,org,net} or
whatever.

I suspect that the correct way to do this would be:

    create table domain (
        id integer primary key,
        name varchar(255)
    );

    create table destination (
        domainid integer references domain.id,
        localpart varchar(255),
        type enum ( forwarder, popbox ),
        destination text,
        primary key (domainid, localpart)
    );

using a horrid mess of different products' proprietary SQL
syntax.

-- 
``The Fifth Law of Pipes: The outside diameter must exceed the inside
  diameter; otherwise the hole will be on the outside of the pipe.''