simple message system table design

[bigtimslim]

I've made a private message system for a site. As it is now I have a table that looks like this:

 

message

message_id

to

from

subject

message

status(to check if read)

datetime_sent

 

I've now decided I want users to be able to see Sent Messages. How would you implement this? I could just work off this table, but when the user who received the message deletes it, the message would be removed from the senders sent messages as well. To solve this I could add column that shows whether the user has deleted it and another for the sender.

 

Or.. would it be better to just create a sent message table similar to the one above?

[corbin]

I would just add a deleted column.

[keeB]

I love this problem. Basically, how do you normalize a database where you have 2 different status for the same message? You don't want to enter the message in to the message table twice, as that's not optimal.

 

Basically, the way that I would implement this is the following (pseudo-code):

 

create table message (
   message_id  primary_key int not null,
   to text null ,
   INDEX idx_message_to (to)
   FOREIGN KEY (to) REFERENCES user(user_id) --set up foreign key, index the to field.
   .
   . -- finish table creation


)


create table message_mod (
    id int primary_key not null,
    message_id int not null, 
-- another foreign key here on message(message_id)
    user_id int not null, 
-- another foreign key here on person/user
    status int not null
)

 

 

So basically, what you have is 2 tables. MESSAGE contains all of the data, who sent it, when they sent it, the contents of the message, etc. MESSAGE_MOD contains the user modifications for the same entry. The satisfies the following case:

 

- Bob sends Larry a message.

- Bob checks his outbox and the status of his message is: Sent

- Larry logs in to system and gets a notification of new Unread message.

- Larry reads the message.

- The status of the message has now been changed for Larry.

- (A cool feature) Bob checks his outbox, and see's that Larry has read is message.

 

The SQL to get changes for user 'some_user_1':

SELECT 
   m.subject,
   mod.status
FROM
   message [m]
LEFT JOIN message_mod [mod] ON
   m.message_id = mod.message_id
WHERE
   m.user_id = 'some_user_1'

 

 

[448191]

It's a pretty good idea, though I would personally recommend something different: 1 table 'message_box' (for both in- and outbox, and whatever else folder you can think of -- think IMAP), 1 table 'messages', voila.

 

A folder entry has an associated status (read, unread, deleted, whatever).

 

Seems simpler yet more flexible to me.

 

Both approaches have an Achilles heel though: orphaned messages. If you don't care for soft-delete ('deleted' flag), you can do a ref count on every delete from a folder, deleting the message itself if there are no references left. Otherwise, you'll have to do some form of garbage collection.

 

[keeB]

In MySQL, when you have a foreign key association you can set it to ON DELETE CASCADE. This is how to ensure everything is deleted in an association.

 

But, in the end, if you're having problems with orphans it's because the app is not properly cleaning up after itself and the restrictions in the database are too light.

[448191]

In MySQL, when you have a foreign key association you can set it to ON DELETE CASCADE. This is how to ensure everything is deleted in an association.

 

But, in the end, if you're having problems with orphans it's because the app is not properly cleaning up after itself and the restrictions in the database are too light.

 

And how do you propose we use constraint to prevent orphans in a many to one relation? Foreign key constraints work great for safeguarding referential integrity, but not for preventing orphaned (or rather 'childless', strictly seen) entries.

 

The table on the 'many' side of the relation (message_boxes or message_mod, same issue), should have a constraint, but associating the ON DELETE CASCADE trigger will only cause entries from THAT table to be removed when the parent row is removed. Even if you remove all references from the child table (the 'many' side), the parent row will stay intact.

 

Maybe it's just me, but I don't see ON DELETE CASCADE helping in this situation.

 

The only way I see this work reasonably is performing a conditional delete after a delete from the child table. E.g.:

 

DELETE FROM messages WHERE message_id NOT IN (SELECT message_id FROM message_boxes);

 

If I'm missing something don't hesitate to put me in my place. I'm only human after all.

[keeB]

We're both Human

 

I made the mistake of misinterpreting what you said before. You're right, there's no way to safeguard from having a orphaned entry in a many-to-one situation where you're deleting all of the 'many'

 

I suppose, the way I have always thought about it was to delete the 'one' and have the CASCADE triggers delete everything downstream.

 

Thinking on it further, I don't think, at least in this particular case, that there should be an issue. Meaning, if a user is deleted, I do not think it should impact the other users history (losing auditing because a relationship was severed)

[448191]

Thinking on it further, I don't think, at least in this particular case, that there should be an issue. Meaning, if a user is deleted, I do not think it should impact the other users history (losing auditing because a relationship was severed)

 

Of course not, but when there are no references to a message left, you have to ask yourself, do I have any need for the message itself left? That's the issue.

 

You can choose to keep the messages for archival purposes, but then I personally would move orphaned messages to a different table, purely for that purpose, using garbage collection. Though unless the client had an archiving requirement, I'd just dump the message. Tables can get pretty huge without issue, but if running for a long time, limits will be tested if you don't clean up your mess.

[corbin]

I may be way over simplifying this, but couldn't it just work like this:

 

-If the recipient deletes it, check if it's in the senders outbox.  If it is, keep it.  If not, delete it.  (Hard delete)

-Do the same for the sender.

 

But then I guess there's the problem of whether or not you want people to have a deleted messages folder that has to be emptied (like a recycle bin style thing).  I personally hate that stuff though.  I deleted it, but it's still there.  Wtf?