[Dbmail-dev] pgsql tables integrity

[lou]

Hi there,

Another thought while i was going through dbmail pgsql scheme and 
dbmail-maintenance
integrity check.

CREATE SEQUENCE alias_idnr_seq;
CREATE TABLE aliases (
    alias_idnr INT8 DEFAULT nextval('alias_idnr_seq'),
    ...
->    deliver_to VARCHAR(250) REFERENCE users(user_idnr),
    ...
    PRIMARY KEY (alias_idnr)
);

CREATE TABLE users (
->  user_idnr INT8 DEFAULT nextval('user_idnr_seq'),
  ...  
  PRIMARY KEY (user_idnr)
);

same for

CREATE TABLE mailboxes (
   mailbox_idnr INT8 DEFAULT nextval('mailbox_idnr_seq'),

->   owner_idnr INT8 REFERENCE users(user_idnr),
        ....
}

along with
CREATE TABLE messages (
   message_idnr INT8 DEFAULT nextval('message_idnr_seq'),

->   mailbox_idnr INT8 REFERENCE mailboxes(mailbox_idnr),
        ...
}

I think using references constraints(foreign keys) will guarantee that the 
message is
going to the right user in the right mailbox and things like unconnected 
message wont
exist rather than undelivered, at least the undelivered might stay in the MTA's 
queue for
second retry where the unconnected will be deleted.

outs: mysql doesnt support it no portability :)

Anyone seeing something wrong in that except the above?


cheers