CODEWITHSUNDEEP
mysqlrelational-database
Jackerbil
Jackerbil

Reputation: 140

One-to-many relationship in MySQL

I need to create a table in MySQL for private messaging between users. I've got a problem when it comes to multiple recipients: I have to create a record for each recipient? For example:

mail_uid = 1 | sender_uid = 6891 |  recipient_uid = 5448 |  text = Lorem ipsum

mail_uid = 2 | sender_uid = 6891 |  recipient_uid = 9128 |  text = Lorem ipsum

Isn't there a way to pu all the ID in one field?

mail_uid = 1 | sender_uid = 6891 |  recipient_uid = 5448, 9128 |  text = Lorem ipsum

And then split the content of the field?

Thank you in advance

Upvotes: 0

Views: 89

Answers (2)

Michelle
Michelle

Reputation: 2900

You could put all the values into one field, but shouldn't. You can either create a record for each recipient or, to reduce duplication, create a separate table. Your mail items table would be mail_uid, sender_uid, and text, and your mail_received (or whatever) table would be mail_uid and recipient_uid, with a record for each recipient.

Upvotes: 0

Randy
Randy

Reputation: 16677

you need one more table.

something like

person
---------
person_id
name
etc.

message
------------
message_id
text

person_message
---------------
sender_id
recipient_id
message_id

Upvotes: 1

Related Questions