Advice on building a chat system

[Adaaam]

Hello,

I’m thinking of building a chat system for a website I’m working on but I need some advice on the database structure and queries.

I’m thinking of having something like this:

users table - id, username, hash
messages table - id, message
users_messages table - sender_id, message_id, recipient_id

query - SELECT messages.message FROM users_messages WHERE recipient_id = $id LEFT OUTER JOIN messages ON users_messages.message_id = messages.id

Does the above code look alright? I haven’t tested it I was just thinking it through first. Also, would a query like that make the database run slow if there was thousands of messages to search through? Hope this makes sense

Thanks,
Adam

[Topcoder]

I would just do a normal inner join, I don’t see why you would need an outer join.

SELECT messages.message FROM users_messages WHERE recipient_id = $id INNER JOIN messages ON users_messages.message_id = messages.id

Another column you might want to add to message table is a “timestamp” - So people can know when the message was sent, you can auto-populate that with a default value.