CODEWITHSUNDEEP
mysqldatabasedatabase-designdatabase-schemadatabase-performance
Casey Flynn
Casey Flynn

Reputation: 14038

Database schema advice for a user-inter-messaging feature on a PHP-driven website

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

Upvotes: 2

Views: 2732

Answers (1)

Joel Brown
Joel Brown

Reputation: 14388

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.

Upvotes: 3

Related Questions