Yukatan data model 0.1

The first and most naive version of the Yukatan data model is based on a very simple model of an email message. According to this model, an email message is simply an entity with the following properties:

• Received, when the message was received
• Date, when the message was sent
• From, who sent the message
• To, to whom the message was sent
• Cc, additional message recipients
• MessageID, unique message identifier
• Subject, subject of the message
• Body, text body of the message

This simple model can be described in SQL as:

CREATE TABLE message (
        received        TIMESTAMP WITH TIME ZONE NOT NULL,
        msgdate         TIMESTAMP WITH TIME ZONE,
        msgfrom         CHARACTER VARYING,
        msgto           CHARACTER VARYING,
        msgcc           CHARACTER VARYING,
        msgid           CHARACTER VARYING,
        subject         CHARACTER VARYING,
        body            TEXT
);

This model is obviously lossy as most of the header information is thrown away and only simple text bodies can be represented. A simple addition to prevent losing any data would be to store the entire original message in some extra field.

Just a bit more advanced version of this approach can be found from 2001 in the Mail2DB script, whose purpose is to feed a simple database with elementary email header information to be used by some kind of a web frontend.