0

I am facing some challenges modeling a table for mapping users and their chatroom. I found two ways to get around the specific challenge I am facing, but with performance concerns. I have added a simplified version of actual scenario below-

Table: membership

Columns:

user_id         int
user_name       varchar
user_role       int
chatroom_id     int
chatroom_name   varchar

constraints:

user_id                     unique: an user can only be in one chatroom
user_name+chatroom_id       composite unique: one chatroom can have only one user with same name

When an user leaves, or kicked from a chatroom, I simply delete the row from membership.

Problem: I want to store the membership record for historical reasons, either on same table or a separate one.


Approach 1:
Keep a column 'status'. values: active, left, kicked
Problem with approach 1: Cannot maintain either of the unique constraints (when the same user joins that same or some other chatroom)

-

Approach 2:
Delete the row and use a trigger to arcive the record to different table
Problem with approach 2: Cannot include the information as to why the record was deleted, as trigger cannot determine that, nor there is any way to pass that info to trigger

-

Approach 3:

INSERT INTO archive (..., reason) VALUES(SELECT from membership where ..)
DELETE FROM membership  

within a transaction, no trigger
Problem with approach 3: Works. But performance concerns- requires three queries, insert and delete performance hits for index, requires separate table.

-

Approach 4:
using two new columns- 'status' and 'uuid'.
uuid will be null when status=active, and when status is updated to any other value, uuid will be populated with a GUID.
Each of two constraints will be changed to be composites including the uuid column:

user_id + state + uuid
user_name + chatroom_id + state + uuid

Problem with approach 4: Works. But performance concerns- too many composite unique indexes. Maybe int instead of varchar (GUID) for uuid would improve performance a bit, but that would require further tricks.


Should I normalize the table further? Or any basic design flaw or lack of database design knowledge that led me to this complexity?

If not, which one is the better approach for this case?

0

Approach 5:

is_in_room_now TINYINT DEFAULT NULL, -- always either 1 or NULL
UNIQUE KEY (user_name,chatroom_id,is_in_room_now)

Nulls are not equal to each other, so you can have infinitely many rows with "the same" (user_name, chatroom_id, NULL) without violating the unique constraint... but you can only set is_in_room_now to 1 for exactly one (user_name, chatroom_id, 1) row.

When the user is removed from the room for whatever reason, set the value to null (not 0).

  • Thanks, that looks like a sleek way to get what I want! Didn't know mysql treats null that way in unique constraints. – user9510058 Mar 18 '18 at 19:03
  • Note that you will also need a surrogate [BIG]INT UNSIGNED NOT NULL AUTO_INCREMENT primary key on the table, since primary key columns can't be nullable. – Michael - sqlbot Mar 18 '18 at 19:23
  • @Michael-sqlbot - Is that UNIQUE KEY reasonable? It says there can be 2 rows -- one for the user being in a particular chatroom, plus one for him not being in. – Rick James Mar 20 '18 at 13:18
  • @RickJames not my most elegant work but that was the idea -- the table could contain n rows representing the user having been in the room in the past. – Michael - sqlbot Mar 20 '18 at 14:20
0

First off, your MEMBERSHIP table doesn't appear to be in third normal form (3NF) because you have attributes that depend on only part of one of your unique keys. You should really have a minimum of three tables:

  • USER
  • CHATROOM
  • CHATTER (or MEMBER or whatever suits you)

The idea is that users exist during (and after) participating in chats - because you have (or want to have) a history of users coming and going. Similarly, chats during and after they are active.

To think of it another way, you wouldn't have a different name for a chat room for every user in that chat room, so why is chatroom_name in a table that depends (partially) on user_id?

Chat Room History Table Structure:

Once you deal with the normalization issue, you can focus on the chat room membership history. Based on the problems that you state with the options you've described, I'm assuming that you need to be able to track users coming and going. Here are the columns you want to have for a table that tracks a user's status in a room, given that they can come and go multiple times.

user_id       int        not null
chatroom_id   int        not null,
entered       datetime   not null,  -- default to current on INSERT
exited        datetime   null,      -- set to current when leaving/kicked...
exit_reason   char(1)    null,      -- Use a code for left/kicked/etc.
PRIMARY KEY (user_id, chatroom_id, entered)

This gives you one table which will perform well enough because of the covering index which includes both user_id and chatroom_id and it lets you maintain active and historical chatroom memberships in one place, so it's simple and efficient. Entering a chat involves just one insert. Leaving a chat involves just one update.

  • yes you're right, the chatroom name is not supposed to be in the membership table, I've moved it to a different table. However, the chat history model you suggested doesn't conform to any of the unique constraints I need. i.e.- multiple active records can exist for an user_id or an user_name in that model. – user9510058 Mar 18 '18 at 18:59
  • @user9510058 You can solve the only one chat at a time per user constraint by adding a FK from USER to MEMBER. However I didn't include this for (a) simplicity and (b) because it seems like an unrealistic business requirement in the real world. You system has your rules, of course. – Joel Brown Mar 18 '18 at 20:58
0

I think about "Entities" and "Relations". Your Entities include User and ChatRoom -- so that gives you two tables. Don't try to merge them together.

Since a user can be in only one (or zero) chatroom at a time, the User table will have chatroom_id as a column in order to provide the "many:1" relationship. Set that column to NULL to indicate the user is in no chatroom.

"Many:many" relationships need an extra table.

Another principle, when you keep "history", is to have two tables (or pairs of tables) -- one for "current state" and one for "history". They will have similar, but probably not identical schema. When doing UPDATE state, also do INSERT INTO history.

  • Initially I went by the general principles as you stated, but what led me to think I might use a separate table to store the memberships is- I will often have to fetch all the users of a chat_room. So instead of having too many Indexed columns in User table, I wanted to move the membership in a separate table and index chatroom_id and user_id columns, even though it is Many:1 relationship. User table already had several other indexed columns like- their email etc. – user9510058 Mar 20 '18 at 10:34
  • Thank you for your ans. I accepted the ans of Michael bcoz it got me around the problem I was having. But I really needed an explanation of what my design flaw is, and your ans helped figure them out. – user9510058 Mar 20 '18 at 10:39
  • One more confusion- is it still Many:1 relationship if I keep the historical/inactive membership records in the same table? – user9510058 Mar 20 '18 at 11:51
  • @user9510058 - You can have a many:1 in a single table -- think about a hierarchy. Each row (except the root) has 1 "parent" and potentially many "children". This involves an id for the row, a parent_id, and a "self-join". That's not the same as having lots of history rows in the same table -- unless you chain them together by which replaces which. – Rick James Mar 20 '18 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.