Structuring mySQL

I have a table with contacts and another one with message. In the message table I have a cell where I keep a serialized string of IDs of those members that the message was sent to.

Now, I am going to append a unique identifier token and when a member responds I'll be able to track which member it is. Shall I store this info in a separate table, or add another cell to message tables, which can also hold a string of responded members?

Logic tells me that a separate table would be easier, but at the same time it can grow pretty fast as there could be multiple 开发者_C百科groups of recipients of messages, and each can have multiple messages, which will translate into multiple rows, as opposed to a single cell with a serialized string.

It will most likely be easier and faster with the separate table.

For example, if you want to find all the messages sent to a specific user it will be a simple query: WHERE recipient = id. This query will perform well because it can use an index.

If you store it as a comma separated list of IDs in a text field you will have to write a query using for example FIND_IN_SET. This query won't be able to use an index.

I think the conventional approach would be to have join tables. So if you have a Contacts table and a Messages table, you could create a ContactMessagesSent table, which has the primary keys of both Contacts and Messages as columns, to keep track of which messages were sent to which contacts. You could create another join table ContactMessagesReceived to track responses.

So

ContactMessagesSent
--------
contact_id ; // primary key of Contacts
message_id ; // primary key of Messages
...

If you use proper indexing, it will perform well even if there are lots of rows in the table.

Create a separate tables. Storing a serialized string of members was a wrong decision in the first place. Querying on such a field is cumbersome and slow.

Make a separate cross link table that binds members to messages, so you can actually query by it. Then, you can add a field 'Responded' to that table to mark if a member responded.