[PLUG] Database design questions

[Stephen Gran <steve@lobefin.net>]

Hello all,

I''m starting to think about setting up a database as a backend for ane
exim installation, with the idea that it would hold the information that
usually goes into sendmail's local-host-names, virtusertable,
relay-domains, and alias files.  I am running into some design problems,
and Ithought I'd run it by you all to see what ideas pop up.

Basically, I've gotten as far as something like:

Table domains
 -----------------------------------------------------------------
| domain         |     local_or_relay       |    destination      |
 -----------------------------------------------------------------
| varchar        |     boolean              |    varchar          |
 -----------------------------------------------------------------

Table users
 -----------------------------------------------------------------
|  uid           |     alias                |     domain          |
 -----------------------------------------------------------------
|  varchar       |     varchar              | domains.domain      |
 -----------------------------------------------------------------

But this sucks.  It means that multiple aliases for an address store
more than one piece of data in a field, and that's terrible.  It also
means that one user can't easily be updated in several domains at once -
say joe has a joe@ address in several domains, and all his email is
forwarded elsewhere - I would have to update all of them individually if
he changes his forwarding address.  A more complicated arrangement, liek
joe has addresses joe@domain, joe1@domain2, and joey@domain3 would also
fail, but I'm not sure what to do with that (work by gecos?  Make a uid
field?  Seperate table that lists all real people, and then have a link
table link that with email addresses and aliases?)

In case my background and rough sketch weren't clear, I would like
to have a database structure that allows routing decisons to be made
for a given address.  The mail server handles several domains, some of
which are delivered locally (usually through aliases), some of which are
forwarded offsite (again, after aliasing), and some of which have manual
routes set up.  The last is for cases where this machine is the MX,
but people want the actual mail to end up somewhere else - this would
be useful to protect an exchange server from ever actually seeing the
internet directly, for example.

I think I may need a link table or something, but database design is
not something I'm good at, so I am looking for help.  I can at least get
as far as seeing some of the problems, but that's not quite enough :)

Thanks,
-- 
 --------------------------------------------------------------------------
|  Stephen Gran                  | A grammarian's life is always in tense. |
|  steve@lobefin.net             |                                         |
|  http://www.lobefin.net/~steve |                                         |
 --------------------------------------------------------------------------

Attachment: pgpCKTyFrKN5s.pgp
Description: PGP signature