How to model a Messaging/Chatting system in C# with Entity Framework

Question

I have a simple question

I'm developping a chat system service but I came across something interesting.

I'm currently operating this way :

First table :

[Messages] :
SenderID(user),
RecipientID(user),
Content(string) 

Of course, everytime a user sends a message to the other user, I add it to the table. But I thought about the fact that If a table has a million lines, it would become a mess

So I thought about another way being :

First Table :

[Conversation]
ConversationID,
USER1(user),
USER2(user)

Second table :

[Messages] in which I have 
ConversationID,
Content(string) 

So basically, I'm asking, which configuration should I use?

Answer 1

The approach below should be able to sort you out. This is a good basis for both chat and messaging, where with chat you can poll recent messages from the client side and slap on an intuitive UI.

Message

Message {
  MessageId,
  FromId, -- Foreign key User.UserId
  ToId, -- Foreign key User.UserId
  Subject, 
  Content,
  Attachment, -- can be null or default to a 0
  DateReceived, -- can be null or default to 1901 or sumin'
  DateRead
  ...
}

User

User {
  UserId
  UserName
  ...
}

Queries

Inbox = Message where ToId = Me.UserId
Sent = Message where FromId = Me.UserId
Conversation = Group by Subject
Attachment = (Simple = Path to attachment file. || Better = DocumentId)

Attachment

Document {
  int DocumentId,
  int DocTypeId,
  virtual DocumentType DocumentType,
  string FileName,
  int UserId,
  string mimeType,
  float fileSize,
  string storagePath,
  int OrganizationId,
  string fileHash,
  string ipAddress,
  DateTime DateCreated = DateTime.Now;
}

Then you run into the issue with group chats. Do you send a message to each recipient of the group or do you create a single message that each recipient has access to?

But we're keeping it simple.

Answer 2

Here is another idea, Store the notion of a "message". Whether it be an email, text, sms or sound file. this is where you would store the message text and or other metadata. Think of the "message" as everything up to releasing the stop talk button on a walkie-talkie to end the transmission and everything up until the start and stop has been stored in the "message". This message could be tied to other messages by the fact that the user was replying to a "message" from a prior party's request to talk.

If you wanted to make the whole relationship easy then you could log the "messages" sent to a user, "inbox" and messages sent from a user, "outbox". This is synonymous with today's messaging mentality.

Message

MessageID, 
Subject, 
Content 
...

MessageReceived

MessageReceivedID, 
UserID, 
FromUserID, 
MessageID, 
DateReceived, 
DateRead, 

MessageSent

MessageSentID, 
UserID, 
MessageID, 
DateSent, 
DeletedStatusID