User Messages Database Schema?

Question

This is probably very subjective and I have my own thoughts on this, but how would one go about constructing a database that supports user to user messages (one to one or one to many).

My original thought was to have a MESSAGE table and MESSAGE_PARTIES table

MESSAGE would include the following fields: -ID, Subject, Body, Created On, Parent MSG ID (for replies)

MESSAGE PARTIES fields would include: -ID, Message ID, Sender ID, Recipient ID, Sent On

Is this sound logic? I'm dying to know what the better/more efficient way is.

Answer 1

You're not quite right with MESSAGE PARTIES. Given your current setup, you're able to specify multiple senders and multiple dates (more precisely, you're able to specify multiple combinations of sender, recipient, and date). This is not like any messaging system I'm familiar with, and likely not what you intended (it also doesn't match your description).

A more suitable layout would put the sender and "sent on" fields in the MESSAGE table, then just ID, Message ID, and Recipient ID in the MESSAGE PARTIES table.