0

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

Fabio Antunes
  • 22,251
  • 15
  • 81
  • 96
Jackerbil
  • 140
  • 8
  • do not put it all in one field. please. – Randy Aug 02 '13 at 14:54
  • 1
    See my answer to [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Aug 02 '13 at 15:26
  • Ahah ok, I've already read that it's not correct to do that, but what is the problem or the risk? EDIT - Ok, thank you Bill. – Jackerbil Aug 02 '13 at 15:29

2 Answers2

1

you need one more table.

something like

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

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

person_message
---------------
sender_id
recipient_id
message_id
Randy
  • 16,480
  • 1
  • 37
  • 55
  • Ok thanks, but creating another table like that, there would still be the same repetition of records. Isn't that the same thing than adding records in the table "message"? – Jackerbil Aug 02 '13 at 15:28
  • 1
    @Jackerbil: Not even a little bit. The purpose of this separation is to follow the principle of atomization. One value per cell. You don't *have* to follow it but you're saving yourself a lot of headache if you do. You're not repeating records, you're repeating keys. Links are cheap. Use them. – Paarth Aug 02 '13 at 16:25
  • Ok, so in the "person_message" table I won't have the text of the message, but just the IDs, and then with the id of the message I can get the text of the message from the other table, is this the difference, right? – Jackerbil Aug 02 '13 at 17:12
  • Yes - that is correct. you use the id as a pointer to the other values. – Randy Aug 02 '13 at 18:19
0

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.

Michelle
  • 2,830
  • 26
  • 33
  • Or the sender can go in the second table, as in Randy's answer. I'm assuming there's only one sender per message, though, so it would be unnecessarily repeated when there are multiple recipient records per mail item. – Michelle Aug 02 '13 at 14:57
  • Exactly. So creating a new table would be the same thing as adding records in the message table, right? – Jackerbil Aug 02 '13 at 15:32
  • @Jackerbil Not exactly. If you have multiple records in the message table, the text column (most likely the largest field) will be repeated for every recipient. If you have a second table, the text is stored only once per message. – Michelle Aug 02 '13 at 15:49