[Vmail-discuss] Separate domain_alias table?

[Paul Makepeace]

On Thu, Jul 10, 2003 at 08:31:31PM +0100, Chris Lightfoot wrote:
> On Thu, Jul 10, 2003 at 08:20:39PM +0100, Paul Makepeace wrote:
> > On Thu, Jul 10, 2003 at 08:08:45PM +0100, Chris Lightfoot wrote:
> > > On Thu, Jul 10, 2003 at 05:28:41PM +0100, Paul Makepeace wrote:
> > >     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)
> > >     );
> > 
> > (Isn't this solving another problem?)
> 
> No, I think they're the same problem. E.g.:
> 
>     insert into domain (id, name) values (1, 'example.com');
>     insert into domain (id, name) values (1, 'example.org');
>     insert into domain (id, name) values (1, 'example.net');

(That's three entries with the same primary key!)

>     insert into destination (domainid, localpart, type, destination)
>         values (1, 'fred', forwarder, 'fred@somewhere.else');
> 
> now creates fred as a destination in example.{com,org,net}.

I get the idea though. (e.g. you made the domain name the primary key.)

I can envisage the following scare scenario:

     insert into domain (id, name) values (1, 'example.com');
     insert into domain (id, name) values (1, 'example.org');
     insert into domain (id, name) values (2, 'example.net');
     insert into destination (domainid, localpart, type, destination)
         values (2, 'fred', forwarder, 'fred@somewhere.else');
     insert into destination (domainid, localpart, type, destination)
         values (2, 'jane', forwarder, 'jane@somewhere.else');
     
So fred@example.net -> fred@somewhere.else
   jane@example.net -> jane@somewhere.else

Now, if I make example.net aliased to example.{com,org}, say with
     update domain set domainid=1 where name='example.net';

..it'll end up with a load of dangling destination references for the
old domainid=2 (e.g. jane). Not a problem with a ANSI SQL RDBMS but a
with MySQL which doesn't have ON UPDATE CASCADE you'd have to
additionally fix every table that REFERENCES domain(id).

(Unless I've missed something neat instead.)

I'm leaning ever toward an alias field in the domain table...

Paul

-- 
Paul Makepeace ....................................... http://paulm.com/

"If monkies had hats, then put it down. It's not yours anyway."
   -- http://paulm.com/toys/surrealism/