CODEWITHSUNDEEP
phpmysqlsqldatabase-designchat
Bucky
Bucky

Reputation: 15

Make Unique key work with NULL values?

I have created chat application and its chat seen sql look like below

chat_seen --table name
--Columns---
from_user  | to_id  | group_id | unseen_count

It works like this:When group_id is NULL it means that this is one to one chat,if group_id is not null from_user is final user which wrote message and to_id is user who is group member.I have Foreign key on group_id and it reference to group table,and I have Unique key for 3 column(from_user,to_id,group_id),When group_id is null it should work because from_user and to_id should be unique in one to one chat ,but in the group chat it should be unique per group_id,this will work if Unique key works with NULL VALUES because if it works it will count group_id NULL values as group_id and per NULL group from_user and to_id will be unique.I cant change group_id to anything because it has foreign key.

I also want to achieve that if group_id is not null to_id should has Foreign key and it should reference to group member,group_id is not NULL it should reference to user_id in user table.

Updated version with examples

When someone in the group write message per message I insert or update(if exists) to chat_seen table for each user,it means that if Group contain 20 people per each message I will update or insert 20 column to notify them later if they dont see chat.And I update unseen_count per each user.

What should I do?any suggestion?

Upvotes: 0

Views: 810

Answers (3)

Rick James
Rick James

Reputation: 142218

unique index allows duplicates with null values - discussed and rejected

Upvotes: 0

Bucky
Bucky

Reputation: 15

I have solved this problem with a little bit hack.When one to one chat insert to chat_seen table first I check from_user is null or not because I allowed from_user can be null(I will explain it later).Then I have created from_user and to_id Unique key.Now I have 2 Unique key 1)from_user and to_id and another is 2)to_id and group_id(I have delete from_user from this unique key).When user insert to chat_seen table with group_chat mod,I insert null to from_user because I want to ignore 1) Uniuqe key for groups,if group is null it means one to one chat 2) unique key will be ignore.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18940

Assign the Id field for groups and the Id field for users from the same autosequence generator. This guarantees that a given Id can be either a user or a group, but not both.

Now eliminate to Group_Id in your table. Instead, just store the groupId in the To_Id field for group messages, and make the From_Id and To_Id unique.

If you want to look at only the messages sent to groups, just join with the groups table. The messages for individuals will drop out of the join.

Likewise, if you want to look at only the messages to individuals, just join with the users table. The messages for groups will drop out of the join.

Upvotes: 1

Related Questions