Multiple values in a column

Question

I need some advice regarding multiple records. I have a table with fields username,message and message_to. The scenario could be sending the same message to multiple users in one go. What do you suggest? Will it be efficient to save all recipients in a single column with comma separated values or I add multiple entries?

Answer 1

No, no, no, no, no.

This would be a capital violation of the relational database model. Create three tables: Users, Messages, and MessagesSentToUsers.

• In the Users table, store a user_id value, and the user's name.
• In the Messages table, store a message_id value and the message text.
• In the MessagesSentToUsers table create on record for each time a message is sent. This record should contain only the user_id for the user who got the message, and a message_id pointing to the message text they received.

Like the following:

|       User     |  |      Message      |  |  MessageSentToUsers   |
|---------|------|  |------------|------|  |----------|------------|
| user_id | name |  | message_id | text |  | user_id  | message_id |

In general, the id columns in each table will be of type integer. The most common case is to have the DBMS assign a value to each row (so you don't have to manage the numbers).