title: design database for a basic mailing system
body: <p>I'm trying to create a simple mailing application, and this is what I need for it : Inbox, Outbox, Sent message, Reply, Delete message, Message was read or not ... </p>
  
  <p>I read some solutions about mailing systems, but it seems that it doesn't clear enough for me (and/or some solutions gives more than what i need) ...</p>
  
  <p>here is my try :</p>
  
  <pre><code>CREATE TABLE Messages(
      msg_id  INT NOT NULL AUTO_INCREMENT,
      from_id INT NOT NULL,
      to_id   INT NOT NULL,
      subject VARCHAR(250) NOT NULL,
      body    VARCHAR(1024) NOT NULL,
      parent  INT NOT NULL,
      date    DATETIME NOT NULL,
      read    TINYINT NOT NULL default 0
      deleted TINYINT,
      --PKs &amp; FKs
  );
  </code></pre>
  
  <ul>
  <li>parent column for reply.</li>
  <li>deleted column accept three values default null, 1 and 0, null for no one delete message, 0 for sender delete message from his outbox, and 1 for receiver delete message from his inbox</li>
  </ul>
  
  <p>I need to know if there is better solutions ...
  (I'm using PHP/Mysql)</p>
  
  <hr>
  
  <h2><strong>Edited</strong></h2>
  
  <p>I thought about it again and I think I found a better solution :</p>
  
  <pre><code>create table messages(
  id int not null auto_increment,--pk
  subject varchar(250) not null,
  body varchar(1024) not null,
  date datetime not null,
  parent_msg int,--fk to messages.id
  );
  
  create table sent(
  msg_id int not null,--fk to message.id
  sender int not null,--fk to user.id
  delete tinyint not null default 0
  );
  
  create table received(
  msg_id int not null,--fk to message.id
  receiver int not null,--fk to user.id
  delete tinyint not null default 0,
  read tinyint not null default 0
  );
  </code></pre>
  
  <ul>
  <li>That's way users can have more options : move message to trash or remove message permanently</li>
  <li>If the user move message to trash we just set delete to true, and if he remove msg permanently we remove its related row from table (sent or received).</li>
  <li>(If sender and receiver remove msg permanently then we delete it from messages table).</li>
  </ul>