Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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:

ERD

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.