Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table for email messages. Then, I have a table that has the message parts.

The Parts table contains a

   field_id,  message_id, and data 

At the time I used Parts to name the table and yet used field_id for the column. Just an FYI

So for example, a part_id of 2 would be the subject of the message. I have parts for subject, date, htmlbody, and textbody. Due to this structure, I have approximately 2 more queries per email (one for the parts, and another for the email addresses associated to the email) than if I were to push all the data into the messages tables. I found this structure to be best, but I'm beginning to think it might be wrong and not best for performance.

My question is, will it be in my best interests to restructure the database? Id rather not.

I was thinking about moving the htmlbody and textbody and subject and date to the messages table. Another solution would be to grab all the emails and their data from the Parts table in one query. I could grab all the ids in one query and then do an IN(ids) for the second query.

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `account_folder_id` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `uid` int(10) NOT NULL,
  `seen` tinyint(1) NOT NULL,
  `flagged` tinyint(1) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `message_parts_data` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `field_id` int(10) NOT NULL,
  `data` text NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `fields` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

INSERT INTO `fields` (`id`, `name`) VALUES
(1, 'To'),
(2, 'Subject'),
(3, 'Date'),
(4, 'From'),
(5, 'CC'),
(7, 'ReplyTo'),
(8, 'textHtml'),
(9, 'textPlain'),
(11, 'Forward');

Thanks

share|improve this question

migrated from stackoverflow.com Jul 10 '13 at 6:58

This question came from our site for professional and enthusiast programmers.

7  
There's no belief system for normalization; it's either normalized or it's not. –  Kermit Jul 10 '13 at 3:25
1  
I think you should provide sample data more than the query itself first. In that regard we could focus if your Database really need re-structuring or not. Then we could move on to refining your query later if you could show it also. –  Edper Jul 10 '13 at 3:31
    
I updated my question. Im pretty sure theres no redundant data, but Im not going to claim its normalized so i just took that out. @Edper I'll get some data tomorrow around 3pm. –  jkushner Jul 10 '13 at 3:32
    
So the data column stores data for all subject, date, htmlbody, and textbody, right? This looks like EAV structure. Please add the design of all the relevant tables you have (Messages, Parts, whatever else), even better the CREATE TABLE statements. –  ypercube Jul 10 '13 at 11:55
    
Added the messages,fields, and message parts table SQL. –  jkushner Jul 10 '13 at 19:33

1 Answer 1

I can't tell you the best structure for your data because I don't know all your needs, however, I can tell you how I structured my tables for a similar project that had to store a scalable amount of email messages in a searchable form that may or may not be geographically distributed. This is what we did:

There was a messages table consisting of a unique message_id as primary key, as well as some fields to store information that was not contained in the message itself (data from the SMTP envelope) such as dates and times, sender, sending server, a few performance indicators, etc. Additionally, there was a storage_id, to indicate which storage structure contained the parts/attachments (usually a folder path, but could be a schema-based address, such as sftp://user@host/path/to/folder).

Next was the headers table which stored one message header line per row. This contained a unique header_id as primary key, as well as a message_id (indexed) that linked back to a record in the messages table. There was a header_name field (indexed), which contained the header name (eg: "Subject", "From", etc), and a header_text field, which contained the text of the header. There was also a sequence number (indexed) that was used to indicate the order of the headers. Due to our need to search these headers, we used fulltext indexing on header_text.

Then there was a parts_text table which stored only the text parts of a message. This table had a unique part_id as primary key, a message_id, a sequence and some fields for storing mime types and part-markers, and the part_body field, which stored the entire text. This field was fulltext indexed, however in the long term we planned to run the message's text parts through a lucene indexing process and just store the results in the db, with the actual text part stored as a file in the area designated by the message's storage_id.

Then there was a parts_binary table which stored the same things as the parts_text table but without the part_body. That was stored as a file in the area designated by the message's storage_id. Note that the sequence in this table would be interspersed with the sequence from the parts_text table.

I know this is not a direct answer to your question, but this structure worked for us so maybe it can be of some help to you.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.