1

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 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.

Improve this question
2
  • 1
    Some of the questions I posted still do not have the answers. If I accept someone who has not answered the question, it'll be devaluing the SO model. Commented Oct 13, 2011 at 20:10
  • Fair enough. You're amongst the very few who only have 70% of their questions answered. :) Commented Oct 13, 2011 at 20:16

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I don't think I'll need a functionality to look for messages that were sent to each recipient. But I will most likely have a need to display who it was sent to and whether there was a response or not. I am starting to lean towards a separate table. What is considered a reasonably large table in mySQL. How many rows can I have assuming just 4 columns with simple digit ids?
1

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.

Improve this answer

1 Comment

depends on how you are going to query the table. You probably want to index by contact_id, message_id, and both (contact_id, message_id)
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.