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.
-
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 justID
,Message ID
, andRecipient ID
in theMESSAGE PARTIES
table.