Database Structure for Web Messaging System
After learning the hard way (times ago, during a final project...), I can advise you to separate and organize the things whenever possible. Self-relationship is a nice thing not to be near, when possible (there are rare exceptions). Design your classes at first instance; build then a database in which things fit well, but keeping things simple as they should be.
My preference is... better drawn than said:
Here is the MySQL Script exported via MySQL Workbench.
A possible query to list messages from a certain header would be
SELECT h.id AS `header_id`, h.`subject`, h.`status`, m.id AS `message_id`, m.content, m.`time`, IF(m.is_from_sender, x.`name`, y.`name`) AS `written_by`FROM (SELECT * FROM header WHERE id = @VAR) h INNER JOIN message m ON (h.id = m.header_id) INNER JOIN user x ON (h.from_id = x.id) INNER JOIN user y ON (h.to_id = y.id);- You'll see a personal preference of mine to bit fields. For instance, you don't really have to remember a certain from_id more than one time, once your purpose is a two person messaging system.