CODEWITHSUNDEEP
mysqldatabase-designchat
l3utterfly
l3utterfly

Reputation: 2186

Database design for chatroom application

EDIT

People think this question is too broad so I'm narrowing things down a bit here:

I have a table with dual primary keys: roomID and userID.

Here is some example data:

roomID | userID

1 | 5

1 | 9

1 | 10

1 | 12

2 | 5

2 | 9

2 | 10

3 | 5

3 | 17

Given a list of users: 5,9,10, how can I return the roomID containing ONLY those users? So in this case it should return 2.

I hope this can be done within 1 SQL query.

Any help would be appreicated.

ORIGINAL QUESTION

I am making a chat room application and need to design a database backend for it. All the rooms are created on the fly and are destroyed when the last user exits the room. Users are able to add other users to any room they are in.

Currently this is my design:

I have a chatroom table with two columns. The two columns are both primary keys for the table (so a row is considered duplicate only when both columns are the same). The first column is the room ID. The second column is a user ID. The idea I have here is with the same room ID, there can be many users in this room (so rows with same room ID but different user ID). When I need to create a new room, I simply select MAX(room ID) + 1 and create a new room with this ID and add the users into it.

Given a list of users IDs (such as 1,5,31,12), I need to find out if a room is already created for them. In other words, I need to determine if there are rows all with the same room ID having users IDs 1,5,31,12. However, if a room is created with users 1,5,31,12,6 (one or more extra users), this should not count as room already created. I will then need to create a new room for them and add the users to that. Same goes for less users than the list.

Right now I'm having trouble forming the query to determine if I need to create a new room or not, and if not, retrieve the room ID of the existing room.

Any help would be appreciated.

Also, I think this design is quite cumbersome, you are welcome to suggest a better database design.

P.S. the database I'm using is MySQL

Upvotes: 0

Views: 1711

Answers (1)

hiennt
hiennt

Reputation: 890

I think yoy can add 1 more col to the chatroom table, name num_member, this is number of member in room( or better have room(room_id, number_member) table). To make it simple first, I assume you have num_member in chatroom. This query might work:

Select * From chatroom where user_id IN ($userIdList) Group by room_id HAVING count(*) = chatroom.num_member

Hope this help

Upvotes: 0

Related Questions