一种更有效的链接mysql表的方法

Question

On my site, there is an area where users can write and send a message to any number of other users. That message is viewable to only the users specified by the message author. So I made 2 tables: Message_Author and message_Receivers and give them a common column of messageID. For every message posted, one entry is inserted into Message_ Author

$db->query("
            INSERT INTO Message_Author
            VALUES('$message_id',$_SESSION[email],$message)
           ");   

and into Message_Receivers:

foreach($receiversUserNameArray as &$value){

   $db->query("
               INSERT INTO Message_Receivers
               VALUES('$message_id',$value)
              ");
}

So now every user specified by the message author can view the message with that message id. The problem is if a user wants to "send to all" contacts. This could potentially be hundreds usernames each with its own row taking up a lot of database space. So is there a better way of linking the messageID between these two tables without making a row for each receiver?

Answer 1

Using a message<>user table IS the proper way to do this, but it might be better to use numeric IDs for both the messages and the users. The layout should ideally look something like this:

User: UserID, UserName, UserEmail, ...
Message: MessageID, Message
Message_Author: UserID, MessageID
Message_Receivers: UserID, MessageID

It might also be worthwhile to roll Message_Author and Message_Receivers together, but ultimately in SQL the whole idea is to have one row per datum - in your case you're trying to connect a message and a user, so one row per connection is quite logical. Using numerical IDs will also make sure these connections won't use up too much space.