Das Benno MailArchiv Wiki

CREATE TABLE customer(
  customer_id VARCHAR(64),
  displayname VARCHAR(256),
  PRIMARY KEY (customer_id)
);
 
CREATE TABLE maildomain(
  maildomain VARCHAR(256),
  customer_id VARCHAR(64),
  FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
  PRIMARY KEY (maildomain,customer_id)
);
 
CREATE TABLE mailbox(
  mailbox_id VARCHAR(64),
  customer_id VARCHAR(64),
  displayname VARCHAR(64),
  password VARCHAR(256),
  FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
  PRIMARY KEY (mailbox_id,customer_id)
);
 
CREATE TABLE alias(
  alias_address VARCHAR(256),
  mailbox_id VARCHAR(64),
  FOREIGN KEY(mailbox_id) REFERENCES mailbox(mailbox_id),
  PRIMARY KEY (alias_address,mailbox_id)
);
 
-- Datenbank Content
INSERT INTO customer VALUES ('123','Hansen und Meyer');
INSERT INTO customer VALUES ('456','LWsystems NET');
INSERT INTO customer VALUES ('789','LWS Example');
 
INSERT INTO maildomain VALUES ('hansen-und-meyer.de','123');
INSERT INTO maildomain VALUES ('lw-systems.net','456');
INSERT INTO maildomain VALUES ('lws.example','789');
 
INSERT INTO mailbox VALUES ('m1','123','M. Hendriks','secretm1');
INSERT INTO mailbox VALUES ('m2','123','J. Kohning','secretm2');
INSERT INTO mailbox VALUES ('m3','123','M. Mentrup','secretm3');
INSERT INTO mailbox VALUES ('m4','456','Paula Schmidt','secretm4');
INSERT INTO mailbox VALUES ('m5','456','Herbert Waltrup','secretm5');
INSERT INTO mailbox VALUES ('m6','789','Benno Iburg','secretm6');
 
INSERT INTO alias VALUES ('m.hendriks@hansen-und-meyer.de','m1');
INSERT INTO alias VALUES ('j.kohning@hansen-und-meyer.de','m2');
INSERT INTO alias VALUES ('m.mentrup@hansen-und-meyer.de','m3');
INSERT INTO alias VALUES ('info@hansen-und-meyer.de','m3');
INSERT INTO alias VALUES ('pschmidtlw-systems.net','m4');
INSERT INTO alias VALUES ('office@lw-systems.net','m4');
INSERT INTO alias VALUES ('herbert.waltrup@lw-systems.net','m5');
INSERT INTO alias VALUES ('@lws.example','m6');