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-
user_id int user_name varchar user_role int chatroom_id int chatroom_name varchar
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.
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)
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
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.
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?