0

I'm trying to make a chat with dialogue fuction as my learning project. Any user should be able to write messages to another user and recieve answers from that user, and no one else should have access to that dialogue. In MySQL, I've created table user with id, login, password and privileges, table message with id, author (FK to user id), message text, time of post, and dialogue id (FK), and also table dialogue with id, FK to one user, FK to another user and additional columns with some parameters. I want to make my chat the way it let me have all messages from two given users to each other with just one query, but in the realization above I have to check two columns to find one user first, and then check all entries with that user to find his interlocutor. Only this query will return me a dialogue PK which could be used to get all messages. I think this realization is totally wrong, but I can't find the way to fix it. I've tried to attach dialogue id to table user, but this made my base even more complicated. What should I do?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Иван Петров
  • 53
  • 5
  • 1
    show your schema because the above helps little – Drew Jul 24 '16 at 22:59
  • Post it as text with an [edit] to the question. Most of us don't hop to a random server with a click :p – Drew Jul 25 '16 at 13:13
  • But puush is not a random server. http://puush.me/ – Иван Петров Jul 25 '16 at 13:27
  • Why is a schema going the route of a picture. We help people with text like [Here](http://stackoverflow.com/q/38568748) . People don't do pictures here and spend wild hours converting stuff by hand. We aren't like free little elves at the North Pole, waiting for silly busy work. I am sure you understand. You may know of a TLD, but that one is not in my book. Again, you are offering pictures. Good luck. – Drew Jul 25 '16 at 13:31

1 Answers1

0

Here is my first schema: http://puu.sh/qdLMw/f9acde605c.png But I've make another table which will containt both interlocutor and dialogue id. When some user write a message to another, a query request line from this table where current user set as sender and his interlocutor set as target, and get a dialogue id from this line. If there is no such line, system will run another query which switch sender and target in their places. And if result is empty again, new dialogue will be created with two lines in interlocutors table linking two users to each other. http://puu.sh/qdORJ/2122558f64.png It looks just slightly better for me, and if someone have an even better answer, please tell me.

Иван Петров
  • 53
  • 5