0

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 ...

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) ...

here is my try :

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 & FKs
);
  • parent column for reply.
  • 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

I need to know if there is better solutions ... (I'm using PHP/Mysql)

Edited

I thought about it again and I think I found a better solution :

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
);
  • That's way users can have more options : move message to trash or remove message permanently
  • 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).
  • (If sender and receiver remove msg permanently then we delete it from messages table).
Az.Youness
  • 2,167
  • 1
  • 24
  • 33
  • Have you considered something like this: http://stackoverflow.com/questions/6541302/thread-messaging-system-database-schema-design ? – Joel Brown Feb 10 '16 at 13:43
  • @Joel Brown yes i read it, but I can't understand how deleting messages as I need can work with provided solution – Az.Youness Feb 10 '16 at 13:45
  • 1
    What value should be in deleted if both sender and receiver have deleted the message from their respective boxes? – gabe3886 Feb 10 '16 at 13:55
  • I think no need for value for this case because we will remove the row entirely ... (for example if sender delete message and deleted value !=null then delete the message from table) – Az.Youness Feb 10 '16 at 14:02
  • -- sorry in example above deleted=1 not !=null :D – Az.Youness Feb 10 '16 at 14:16
  • You could either modify the `READ_STATE` table to include a deleted flag or you could create a table similar to `READ_STATE` that handles being deleted as opposed to being read. This all presumes that you want to keep data around rather than discarding it. – Joel Brown Feb 10 '16 at 15:16
  • @JoelBrown thnx, but can you plz simply your idea by showing me tables : table1(col1,col2,...) table2(col1,col2,...), and in my case the existence of thread tables are necessary ? – Az.Youness Feb 10 '16 at 15:32

0 Answers0