Mysql – Challenge with modelling a basic membership table

database-designinnodbMySQL

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?

Best Answer

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).

Related Question