2

I have a user driven website. I want to add a messaging feature. My concerns revolve around my database's manageability and performance.

I'm thinking about organizing it like this: I have my users table and all my users have unique id's Any user-initiated conversation between two users would have a record in 'conversations' and all messages in that conversation would use foreign keys that reference that conversation_id.

Can anyone think of any reasons not to go with this approach? I'm wondering how well it will perform long-term, provided I use indexes and limit my queries to ~20 results for each page.

+--------+
| users  |
+--------+
|users_id|
|        |
+--------+

+-------------+
|conversations|
+-------------+
|id           |
|user_id1     |
|user_id2     |
+-------------+

+----------------------+
|conversations_messages|
+----------------------+
|id                    |
|conversations_id      | 
|message_body (varchar)|
|message_time (unix timestamp
+----------------------+

Edit: I've realized I have no way to track who's the sender/receiver. My first instinct is to simply add a 'sender' column to conversations_messages

Casey Flynn
  • 13,654
  • 23
  • 103
  • 194
  • I have concerns about the 'user_id1' / 'user_id2' columns. If you ever want more than two people in the conversation, you'd have to change it, and to find all conversations a single user is in, you'll have to do check both columns. – Doug Kress Aug 25 '11 at 02:51
  • @Doug Kress, that's an interesting point. Do you have any proposed alternatives? – Casey Flynn Aug 25 '11 at 03:01

1 Answers1

3

Have a look at my answer to this question. I address a similar design problem there. It includes handling of who has seen which messages and who is the sender of each message.

Community
  • 1
  • 1
Joel Brown
  • 14,123
  • 4
  • 52
  • 64