Messaging Database - How to handle multiple recipients

Question

So I am trying to develop a relational database (I'm using MySQL, planning on a Java front end) where I have 3 tables (People, where I store names and relevant personal information... and Messages (where for every message sent, the relevant information is stored. Each message is given an ID# from which you can draw the date and time sent, the content of the message, the sender of the message, and the recipient of the message).

My problem is setting up the database such that each message can have just one sender but multiple recipients. For instance, I can easily handle message ID 73 being sent from User 6 to User 3. But how would I handle if message ID 74 was sent from user 4 to users 2,3, and 8? Is there a way to allow multiple recipients? Would I need to rethink how the database is structured? Anyone got some advice/tips?

Answer 1

You need to have a many-to-many table for your message recipients. Think about it this way, each message can be sent to one or more recipients. Also, each recipient can presumably receive zero, one or more messages. This means you have a many-to-many and you need an intersection table to record this information in your database.

Consider this ERD:

Answer 2

You need a many to many relation ship between meshes and recipients which requires an extra take to manage, something like so:

Messages         Recipients         People
----------       -------------      -------------
MsgID (pk)  <--- MsgId (fk)     ,-> PersonID (Pk)
                 PersonID (fk) -'

The table that manages the relationship is usually called a junction table or intersection table - see Wikipedia page about Junction table amongst other places. The pattern is very common so you will need to understand it well if you do much database work.