Yukatan data model 0.5

One missing part in the previous version of the Yukatan data model is that they contain no message transport information. Information about the message source and destination along with a correct delivery timestamp is important message metadata, that in many cases is not readily (or even correctly!) available in the header fields of the message. Thus it would be nice to have this transport information included in the data model whenever it is available.

The three most valuable pieces of transport information are the envelope sender and recipient, and the delivery timestamp. They are even quite simple to include in the model as each of them is a singular data item. See the envsender, envrecipient, and msgtimestamp fields in the SQL schema below.

There are actually also other types of messages than just ones delivered by SMTP or some other message delivery mechanism. For example messages can be saved as drafts, templates or sent mail. Additionally messages can be imported from other message stores without such type information. To manage this information a separate type field is added with the following permitted values: "received", "sent", "draft", "template", "saved". Other message types can be added later on as the data model is extended.

SQL schema

CREATE TABLE message (
        msgno           SERIAL PRIMARY KEY,
        msgtype         CHARACTER VARYING NOT NULL
                        CHECK (msgtype = 'received' OR
                               msgtype = 'sent' OR
                               msgtype = 'draft' OR
                               msgtype = 'template' OR
                               msgtype = 'saved')
                        DEFAULT 'saved',
        msgtimestamp    TIMESTAMP WITH TIME ZONE NOT NULL
                        DEFAULT CURRENT_TIMESTAMP,
        envsender       CHARACTER VARYING,
        envrecipient    CHARACTER VARYING,
        msgdate         TIMESTAMP WITH TIME ZONE,
        msgsendername   CHARACTER VARYING,
        msgsenderaddress CHARACTER VARYING,
        msgmessageid    CHARACTER VARYING,
        msgsubject      CHARACTER VARYING,
        msgbody         TEXT,
        msgsource       BYTEA
);
CREATE TABLE headerfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        fieldno         INTEGER NOT NULL,
        fieldname       CHARACTER VARYING NOT NULL,
        fieldbody       CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, fieldno)
);
CREATE TABLE inreplytofield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        inreplytono     INTEGER NOT NULL,
        inreplytoid     CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, inreplytono)
);
CREATE TABLE referencesfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        referencesno    INTEGER NOT NULL,
        referencesid    CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, referencesno)
);
CREATE TABLE fromfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        fromno          INTEGER NOT NULL,
        fromname        CHARACTER VARYING,
        fromaddress     CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, fromno)
);
CREATE TABLE replytofield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        replytono       INTEGER NOT NULL,
        replytoname     CHARACTER VARYING,
        replytoaddress  CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, replytono)
);
CREATE TABLE tofield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        tono            INTEGER NOT NULL,
        toname          CHARACTER VARYING,
        toaddress       CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, tono)
);
CREATE TABLE ccfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        ccno            INTEGER NOT NULL,
        ccname          CHARACTER VARYING,
        ccaddress       CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, ccno)
);
CREATE TABLE bccfield (
        msgno           INTEGER NOT NULL
                        REFERENCES message
                        ON UPDATE CASCADE ON DELETE CASCADE,
        bccno           INTEGER NOT NULL,
        bccname         CHARACTER VARYING,
        bccaddress      CHARACTER VARYING NOT NULL,
        PRIMARY KEY (msgno, bccno)
);

Notes

Note that this schema moves the NOT NULL constraint from the msgdate field (that is based on the "Date" header field) to the msgtimestamp field. This again makes the data model a bit more robust as there are no real guarantees that the "Date" header field actually exists (or contains a correct value), but all messages can be timestamped when they are entered in the database.