two person chat room

As you are not provided much information about your requirement,I am assuming that you what to create a chat room for two users and these two users can only participate in this room.For this you should not user this schema.

You need to create different tables of different entities and there
relationship.

Here you have two entities.

  1. User
  2. room

And one relation between them.

  1. Chat room

room_table(Insert when room is created)

        id          -room id
        name        -room name
        created_at  -time of creation
        status      -is this room is active or not
        etc

user_table(Insert when user created)

        id          -user id
        name        -user name
        password    -password
        created_at  -time of creation
        status      -is this user is active or not
        etc 

chat_room(Insert when user is assigned in room)

        id          -user id
        room_id     -room_id//reference of room.id
        user1_id    -user_id//reference of user.id
        user2_id    -user_id//reference of user.id
        created_at  -time of creation
        status      -is this chat room is active or not
        etc 

now for any user you to get the chat_room by:

 select id from chat_room where room_id = '$room_id' and user1_id ='$user_id' or user2_id ='$user_id';

Hope this will help you and other. I am wrong any where any one correct me.

Edit:

messages(Insert when sending message)

        id              -user id
        chat_room_id    -id of chat room//reference of chat_room.id
        user_id         -user_id//reference of user.id
        message_text         -text message
        created_at      -time of creation
        status          -is this chat room is active or not
        etc 

Now for any chat room you can get messages by :

select message_text from messages where chat_room_id = 'id_you_got_from_above_query';

Leave a Comment