Re: saving emails in an Oracle table

> Oracle 11g R1
> RHEL (not sure of version)
>
> We are in the process of designing a table(s) in Oracle to store all
> the information pertaining to saving everything related to an email
> being sent out. The idea is to
> gather everything for the email that needs to go out,
> save it to this table,
> select from this table and send the email out using java code or
> oracle stored procs.
>
> What I am wondering is that, are they standard templates out there
> that are being followed for a table to accomplish this. I searched on
> Google and Ask Tom, and found the pl.sql code to send the email but
> not a table definition to store the said email
>
> Here is what I can think the column list should be. Please let me know
> if I am going overboard or if I am missing something. I took some of
> the parameters from the UTL_MAIL package and made them into columns in
> the table.
>
>
> --------------------------------------------------------------------------------------------------------------------------------------
> Here is the spec of the package I used
>
> UTL_MAIL.SEND_ATTACH_VARCHAR2 (
> sender IN VARCHAR2 CHARACTER SET ANY_CS,
> recipients IN VARCHAR2 CHARACTER SET ANY_CS,
> cc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
> bcc IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
> subject IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
> message IN VARCHAR2 CHARACTER SET ANY_CS,
> mime_type IN VARCHAR2 DEFAULT 'text/plain; charset=us-
> ascii',
> priority IN PLS_INTEGER DEFAULT NULL
> attachment IN RAW,
> att_inline IN BOOLEAN DEFAULT TRUE,
> att_mime_type IN VARCHAR2 CHARACTER SET ANY_CS DEFAULT
> 'application/octet',
> att_filename IN VARCHAR2 DEFAULT NULL);
> --------------------------------------------------------------------------------------------------------------------------------------
>
> <primary key col> - NUMBER
> from - varchar2(200)
> reply_to - varchar2(200)
> subject - varchar2(200)
> priority - varchar2(20)
> mime_type - varchar2(20)
> messageContent - clob
> toList - varchar2(4000)
> ccList - varchar2(4000)
> bccList - varchar2(4000)
> att_inline - varchar2(1)
> att_mime_type - varchar2(20)
> create_date - date
> create_user - varchar2(30)
> email_sent_indicator - varchar2(1)
> email_sent_date - date
>
> A supporting table for this would be one where the attachments are
> stored, and we already have one in place for that.
>
> Thanks in advance for your help.
>

I don't believe there is sufficient information to really assess this design. The design is not just about identifying columns, their types and sizes. You also need to consider how the data is to be used.

For example, if you need to select all messages sent to a specific recipient, then your design with a single column representing a list of recipients will be a pain as you will have to extract the column and then use something to break up all the entries and then search through them to find a specific recipient. Indexing would also be a pain.

Where does all this address data come from? Is there already a table manageing names and associated addresses? If so, then don't duplicate this data here.

consider what happens when people change their email address. If you want to find all messages sent to fred smith, how would you do that? What happens if he changes his email address?

do you really need a mail sent indicator and a sent date? Could a null sent date indicate the message has not been sent and a non-null value inicate it has been?

Is there any benefit to storing the content in the database or would it be better to store it (possibly formatted) as a regular file and only maintain meta data about the message in the database?

Is 4k enough to fit all the recipients in a to/cc/bcc field? some addresses can be quite long. what do you do if its not?

What fields are you going to search on? Do the types you have selected make that searching easier or harder?

What happens when messages bounce? do you care?

What types of attachments are you going to have? How will they be 'attached' and from where? do they need to be stored as well? How large are they going to be?

Have you looked at the RFCs for mail and MIME? Does your table contain all the data your java/plsql will need to correctly format the mail so that it meets the standards? for example, please please don't just to an HTML only message - follow the specs and make sure it is multi-part with both a plain text and html part.

What about character sets? What about mail RFCs and character encodings?

How long is the data to be kept. this can be a very difficult issue. Some people will argue it should be kept forever or for seven years. Others will point out that keeping too much data can be a burden. for example, consider what happens if you get a court order to provide all data relating to X. If you have a huge amount of data, just complying with the request cold cost the company thousands - sometimes keeping data can be a bad thing. On the other hand, maybe the business is one where huge archives of data may be very useful for planning/business inteligence/warehousing etc

consider all Oracle's features - nested tables, xml types, supplied packages etc. Then map out how the data is to be used and then look at how Oracle can provide the functionality you need. This should then give you a good starting point for deciding how your data needs to be organised, which will in turn identify design requirements. Once you ahve all of that and provided you keep referencing the basic normal forms, you should be able to identify a good design for the tables. However, just designing the tables based on the parameters for existing procedures is really putting the cart before the horse. nearly everytime I've used the PL/SQL mail package, the procedures are inside higher level ones, so just looking at the low level package is likely to be misleading. identify the required kfunctionality and go forward form there rather than going from available functionality in the database and trying to work back the other way. It is possible you may find the provided package is insufficient and you will instead use some other java class library, in which case, the argument list to the utl_mail package is of no real significance . functionlaity

HTH Tim

-- 
tcross (at) rapttech dot com dot au