Hey, I have a messages table that uses the primary key as an auto increase for the message id but I want to track users conversations so I can display a message history. To do this I'd need 2 unique columns and I can only think that to do this I would need check if an randomly generated 6 digit number existed before insert, this could cause problems if many users are sending messages at once as there could be a mix up? How would you advise to do this?
I would make a another table 'conversations' with an auto incrementing field as the primary key. Then I would make another new table 'conversationmessages' which would be made of two fields that hold the data from your primary key in your message table and the data from your primary key in your conversation table. Those two numbers will let you know what conversation the messages belong to.